Formula based on 3 criteria

[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

 

[Kevin Stecyk]

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

 

[Guest]

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

 

[Kevin Stecyk]

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

 

[Harlan Grove]

...
...

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

...

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

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