IF Function

[pvprasad_26]

Dear Frineds,

I need to implement if Function on a table as
c d e
5 m n p
6 0 6 10

if I m using if formula to find out the largest then m,n,p will be displayed

IF(C6>D6,C5,IF(AND(C6>E6),C5,IF(AND(D6>E6),D5,IF(AND(D6>C6),D5,IF(AND(E6>C6),E5,C6)))))

When i am using the above formulae, I m not getting 10 the largest, please correct me where i m wrong.

[Glenn Kennedy]

iS THIS WHAT YOU WANTED?
=INDEX(C5:E5,MATCH(MAX(C6:E6),C6:E6,0))

[pvprasad_26]

Dear Glenn,

Thnaks Sir it was working it is in same row, if alternate row how this formulae work.

[Glenn Kennedy]

Can you explain what you mean by that a bit more clearly?

[pvprasad_26]

Dear Glenn,

Thanks Sir,

i m using which is the max of three in alternate column of a same row
Attached "if statement.jpg"If statement.jpg

[Glenn Kennedy]

If your data are in A1 to F1, then is this it:

=INDEX(A1:F1,MATCH(MAX(A1:F1),A1:F1,0)-1)

[pvprasad_26]

Thanks Glenn for your help

Please find attached book2.xls I need the answer as required. how to use the formulae as given in red.

[Glenn Kennedy]

To return the maximum rate use this array formula:
=MAX(IF(B12:G12="rate",B13:G13))

To return the supplier of that rate, use this array formula:
=INDEX(B11:G11,MATCH(MAX(IF(B12:G12="rate",B13:G13)),B13:G13,0))

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[JohnTopley]

Based on your example ...

=INDEX(B11:G11,,MATCH(MAX(B13:G13),B13:G13,0))

Note this works because the amount is proportional to the rate (i.e. Rate * 5). Is this always true i.e the greater the rate, the greater the amount?

And please don't use merged cells. The letters are now in columns C, E and G for the above formula.

[pvprasad_26]

Thanks Glenn,

I want to know Why we use CTRL+SHIFT+ENTER in the above formula and why we can't simple ENTER .

[Glenn Kennedy]

Using MAX-IF requires the formula to be array entered, to facilitate repeated calculations. If you don't want an array formula, for whatever reason, you could also try AGGREGATE.

[pvprasad_26]

Thanks Glenn for your reply for doubt,

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.