Returning the highest SUM from a Nested IF

Status
Not open for further replies.

SG 2011

New Member
Joined
Aug 25, 2011
Messages
6
I have a nested IF that checks the values of different cells, and accordingly SUMS a combination of different ranges.

As in the example below, there are 2 conditions, is there a way to make sure it returns the value of the highest SUM product?

=IF(AND(AW7=1,(OR(AND(AO7>=1,AU7>=1)))),SUM(B7,LARGE(C7:R7,{1}),LARGE(S7:AB7,{1})),IF(OR(AND(AO7>=0,AU7>=3)),SUM(B7,LARGE(S7:AB7,{1,2,3}))))


Perhaps I could incorporate a MAX function at the beginning of my formula? Would that work?



Many thanks,

Shyam
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Try:
Code:
=MAX(IF(AND(AW7=1,(OR(AND(AO7>=1,AU7>=1)))),SUM(B7,LARGE(C7:R7,{1}),LARGE(S7:AB7,{1})),0), IF(OR(AND(AO7>=0,AU7>=3)),SUM(B7,LARGE(S7:AB7,{1,2,3})),0))
 
Upvote 0
Hi Joe,

Many thanks for your reply.

Can I just ask would this work if there were 3 SUM conditions as in:

=IF(AND(AW7=3,(OR(AND(AO7>=6,AU7>=0)))),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6})),IF(OR(AND(AO7>=5,AU7>=2)),SUM(B7,LARGE(C7:R7,{1,2,3,4,5}),LARGE(S7:AB7,{1,2})),IF(OR(AND(AO7>=4,AU7>=4)),SUM(B7,LARGE(C7:R7,{1,2,3,4}),LARGE(S7:AB7,{1,2,3,4})))))


I did try inserting the MAX function ahead of each of the IF's, but when I ran the calculations, they still came out wrong?

Please help...


Try:
Code:
=MAX(IF(AND(AW7=1,(OR(AND(AO7>=1,AU7>=1)))),SUM(B7,LARGE(C7:R7,{1}),LARGE(S7:AB7,{1})),0), IF(OR(AND(AO7>=0,AU7>=3)),SUM(B7,LARGE(S7:AB7,{1,2,3})),0))
 
Upvote 0
.. returns the value of the highest SUM product?
I don't understand what you want. Your current formulas check the values in a number of cells (AW7, AO7, AU7) and, depending on those values, calculate a SUM. That is, your formulas return a single number.

What do you mean by the "highest" SUM?

What does "product" have to do with it?

BTW, your existing formulas could be simplified a little. The red bits don't achieve anything and could be removed.

=IF(AND(AW7=1,(OR(AND(AO7>=1,AU7>=1)))),SUM(B7,LARGE(C7:R7,{1}),LARGE(S7:AB7,{1})),IF(OR(AND(AO7>=0,AU7>=3)),SUM(B7,LARGE(S7:AB7,{1,2,3}))))

=IF(AND(AW7=3,(OR(AND(AO7>=6,AU7>=0)))),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6})),IF(OR(AND(AO7>=5,AU7>=2)),SUM(B7,LARGE(C7:R7,{1,2,3,4,5}),LARGE(S7:AB7,{1,2})),IF(OR(AND(AO7>=4,AU7>=4)),SUM(B7,LARGE(C7:R7,{1,2,3,4}),LARGE(S7:AB7,{1,2,3,4})))))

Finally, what do you want returned if none of the conditions are met?
Currently, your formulas just return FALSE in that case (remove the value from AO7, AU7 and AW7 to see)
 
Last edited:
Upvote 0
Having spent some time trying to understand what you have and what you might be trying to do then posting the above, I now discover that you appear to have replaced this question with post #21 here, where the converstaion has, to some extent at least, already covered what I have here. This is why we don't like duplicates.

Questions of a duplicate nature will be locked or deleted, per the Posting Guidelines (especially points 21-23) and Forum Rules (#9).

Any bumps, clarifications, or follow-ups should be posted back to the linked thread.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top