Formula based on 3 criteria

  • Thread starter Thread starter SARAH
  • Start date Start date
S

SARAH

Hi
I need a formula to calculate based on which criteria applies
ex..

If the value in cell E342 is <37 make E345 = E342-37
If the value in cell e342 is >44 make e345 = 37-e342
If the value in cell e342 is between 37 and 44 make e345 = 0

Hope this makes sense
This is as far as I got
=SUMIF(E342,"=<37")

Thanks
 
Hi Sarah,

=IF(E342<37,E342-37, IF(E342<=44,0,37-E342))

I think that works. The first part tests to see if less than 37. The less
than equal to 44 (meaning greater than 37 but less than equal 44), and
finally greater than 44.

Hope that helps.

Regards,
Kevin
 
Hi
Thanks - I couldn't get it to work but it was close.
I modified it to be >=IF(E342<37,E342-37, IF(E342>=44,E342-44))
But now if the result is between 37 and 44 it comes up as
false - I'd like that to be 0
Any suggestions...
Thanks

Sarah
 
Sarah,

=IF(E342<37,E342-37, IF(E342>=44,E342-44,0))

Now it say, if E342<37, then E342-37
if E342>=42, then E342-44
if 37<=E342<44 then 0

You might to use the original with slight mods...

=IF(E342<37,E342-37, IF(E342<44,0,E342-44))

Hope that helps.

Regards,
Kevin
 
...
...
If the value in cell E342 is <37 make E345 = E342-37
If the value in cell e342 is >44 make e345 = 37-e342
If the value in cell e342 is between 37 and 44 make e345 = 0
Click to expand...
...

So you always want a negative or zero result? One possibility,

E345:
=-MAX(0,ABS(E342-40.5)-3.5)
 
Back
Top