CODEWITHSUNDEEP
excelif-statementexcel-formulamax
Tyler
Tyler

Reputation: 616

How can use IF & MAX together?

How do I perform the following function in Excel? Currently Column C display the MAX value of D4:F4. However I need it to display an alternate value.

For instance if MAX Value is from column D, it'll display "TRAN", or if MAX value value comes from column E, It'll display "VERT" and lastly if it comes from column F, it'll display "LONG"

VM

Thanks in advance

Upvotes: 0

Views: 281

Answers (2)

betapig
betapig

Reputation: 53

I would suggest using index-match or vlookup to make a neat formula, avoiding many if statement. Put "TRAN", "VERT", "LONG" above the cooresponding column and use formula below:

=INDEX($D$1:$F$1, MATCH(C4,D4:F4,0))

enter image description here

The result would be enter image description here

Upvotes: 1

QHarr
QHarr

Reputation: 84465

If values cannot tie (i.e. the max value can only occur once in the range you can use the following formula in C4 for example:

=CHOOSE(MATCH(MAX(D4:F4),D4:F4,0),"TRAN","VERT","LONG")

You would then drag that formula down as many rows as is required.

If there can be repeated Max values e.g. D4 and E4 were both to contain 1.439, you would need a specify how to handle this and an adapted/different formula would be required.

Example of checking for this and handling:

 =IF(COUNTIF(D4:F4,MAX(D4:F4))>1,"MyChosenResponse",CHOOSE(MATCH(MAX(D4:F4),D4:F4,0),"TRAN","VERT","LONG"))

Upvotes: 1

Related Questions