Solved Need help with Excel formula using IF and AND functions

April 26, 2017 at 11:46:24
Specs: Windows 7
Example: I need a cell J3 to calculate as follows:
If H3 is >0 and/or <8, J3 should be the sum of 8-H3
IF H3 is =0 J3 should be 0
IF H3 is >8, J3 should be 0

I have come up with =IF(H3<8,SUM(8-H3),IF(H3>8,SUM(0)))
I can't figure out how to modify the H3<8 part to be if it is >0 but <8 so that I can then add IF(H3=0,0)


See More: Need help with Excel formula using IF and AND functions

Report •

#1
April 26, 2017 at 12:08:03
✔ Best Answer
Assuming that H3<0 is never going to happen, try this:

=IF(OR(H3=0,H3>8),0,8-H3)

You've asked for 2 specific conditions where the result should be 0: H3=0 or H3>8, so the OR function handles those. If either of those arguments are TRUE, the OR is TRUE and 0 will be returned.

If both H3=0 and H3>8 are FALSE, then H3 must be >0 and <=8, so there is no reason to specifically test for H3<=8.

P.S. The SUM functions in your formula serve no purpose other than to slow Excel down. Excel knows what to do with 8-H3 and 0.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
April 26, 2017 at 13:00:20
You are AWESOME! Thank you SOOOOO much. That worked perfectly. :)

Report •
Related Solutions


Ask Question