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

DarinOBrienAsked:
Who is Participating?
 
jppintoConnect With a Mentor 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
 
dj_alikCommented:
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
 
thegofCommented:
=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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
DarinOBrienAuthor 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
 
thegofCommented:
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.,
IF(test1a=TRUE, IF( (test1b)=TRUE, answer#1,answer#2), answer#3)

0
 
Rob HensonFinance AnalystCommented:
Would it not be easier to use MAX and/or MIN statements to decide which cell to multiply by.

Cheers
Rob H
0
 
DarinOBrienAuthor Commented:
Thanks for the help...that worked perfectly.
0
 
thegofCommented:
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
 
DarinOBrienAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.