Formula Help - SUMIF

[slow386]

Hi Folks:

I have a row of 6 columns to add (E thru J)
The values of each cell goes from 0 to 5 in 0.5 increments.

If the value of E is less than 1.5, I'd like to just do s straight addition
(max value 30.0)
If the value of E is greater than 2.0, then I would like to double the added
value of get 60.0

What I'm using is =IF(E12<2,,(E12+F12+G12+H12+I12+J12)*2) which works. . .

However, if E12 is under 2.0, then it evaluates to 0 instead of 30

Any help wd be appreciated - Thanks

 

[Dave Peterson]

Maybe...

=MAX(30,(SUM(e12:j12)*2))*(1+(e12>2))
or
=MAX(30,(SUM(e12:j12)*2))*if(e12>2,2,1)

so it takes the sum of e12:j12 and then makes sure it doesn't exceed 30.

Then if E12 > 2, it doubles it. If E12 <= 2, then it just leaves it alone.

(You're going to have to think about whether the cutoff is 1.5 or 2.)

 

[slow386]

Dave:

It's a start, but I must be doing something wrong becasue with everything
set to 1.5, I'm getting 30.
Can I just get it to add straight accross unless E is 2.0 or more??
Thanks very much

Steve

 

[slow386]

Dave:

Found the fix . . .
Thanks

Maybe...

=MAX(30,(SUM(e12:j12)*2))*(1+(e12>2))
or
=MAX(30,(SUM(e12:j12)*2))*if(e12>2,2,1)

so it takes the sum of e12:j12 and then makes sure it doesn't exceed 30.

Then if E12 > 2, it doubles it. If E12 <= 2, then it just leaves it
alone.

(You're going to have to think about whether the cutoff is 1.5 or 2.)

 

[Dave Peterson]

Using Min instead of Max???

Sorry about the typo.