Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 377

# How to combine nested IF statements in Excel

I've got the following equations:

1.  =IF(AND(G79<\$C\$38,SUM(G79:H79)<\$C\$38),\$C\$37*H79)
2.  =IF(AND(G79<\$C\$38,SUM(G79:H79)>\$C\$38),\$C\$37*(\$C\$38-G79))
3.  =IF(G79>\$C\$38,\$C\$37*\$C\$38)

They all appear to work by themselves but I can't figure out how to put them together in one equation in the same cell.

Any help would be much appreciated!

thanks,
darin

0
DarinOBrien
1 Solution

Commented:
Look here:
How to use nested IF statements in Excel with AND, OR, NOT
http://www.experiglot.com/2006/12/11/how-to-use-nested-if-statements-in-excel-with-and-or-not/
0

Commented:
=IF((G79<\$C\$38),IF((SUM(G79:H79)<\$C\$38),\$C\$37*H79,\$C\$37*(\$C\$38-G79)),\$C\$37*\$C\$38)

Essentially, =IF(expression,true_result,false_result)
Both true_result and false_result can be expressions themselves.
0

Author Commented:
I checked out the link and it all makes sense and it looks like this should work:

=IF(AND(G79<\$C\$38,SUM(G79:H79)<\$C\$38),\$C\$37*H79),IF(AND(G79<\$C\$38,SUM(G79:H79)>\$C\$38),\$C\$37*(\$C\$38-G79)),IF(G79>\$C\$38,\$C\$37*\$C\$38)

unfortunately, I get a "#VALUE!" error.

Can you confirm that I'm doing that correctly?
0

Commented:
No, you've got
=IF(),IF(),IF()
which is an error. Try the one I posted.  It uses each of your tests, with the test for 1&2 broken into their second test (your AND() expression) as a separate, nested, IF() statement, e.g.,

0

Commented:
Not tested:

=IF(G79>C38,C37*C38,IF(AND(G79<\$C\$38,SUM(G79:H79)<\$C\$38),\$C\$37*H79,IF(AND(G79<\$C\$38,SUM(G79:H79)>\$C\$38),\$C\$37*(\$C\$38-G79))))
0

IT & Database AssistantCommented:
Would it not be easier to use MAX and/or MIN statements to decide which cell to multiply by.

Cheers
Rob H
0

Author Commented:
Thanks for the help...that worked perfectly.
0

Commented:
Just a matter of curiosity (i.e., I don't care about the points), did my solution not work?  It's simplified to remove the redundant expression evaluations. Force of habit from programming so long, but you don't need the AND statements since they are testing the same two expressions in both.
0

Author Commented:
Hi thegof:

I feel really bad about this.  In hindsight I should have given you points too.  Honestly, I'm such a beginner at Excel so when I was able to copy and paste the formula from ippinto, that's what I did because it was the easiest for me to do.  The form was also more intuitive to me (but again, I'm a beginner with excel).  I don't doubt that your solution would have worked too.  Please accept my apologies.  Is there something I can do to make it up to you?

thanks again,
darin
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.