Returning the highest SUM from a Nested IF

[SG 2011]

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

 

[JoeMo]

Try:

=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))

 

[SG 2011]

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:

=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))

 

[Peter_SSs]

.. 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)

 

[Peter_SSs]

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.