Learn how to a build a cloud-first strategyRegister Now

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

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
Asked:
DarinOBrien
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
jppintoCommented:
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
 
Rob HensonIT & 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
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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