If formula or macro to calculate values in cells

=MAX(A2:E2)

Sorry, I should have read the question properly!

In A2.

IF(MAX(B:B)>MAX(C:C), "b", IF(MAX(C:C)>MAX(B:B), "c", "")

you can return the column number easily enough, e.g.

=MATCH(MAX(A1:Z1),A1:Z1)

I just need to work out how to translate this to a column letter!

...or with no headers but data in A1:Z1 then, adapting Runrigger's approach, you can use thios formula to get the column letter

=SUBSTITUTE(ADDRESS(1,MATCH(MAX(A1:Z1),A1:Z1,0),4),"1","")

regards, barry

Are the A, B, C...etc column references or are they cell entries?

Is a macro solution acceptable?

Or, if you didn't want to have a header;

=LEFT(ADDRESS(1,MATCH(MAX(A1:E1),A1:E1,4),4),1)

The above formula does of course assume the the column letter will be single;

a,b,c,d, etc etc

You would need to slightly modify if your range spilled over in to columns;

aa,ab,ac etc

Barry, you always seem to outsmart me, author - Barry's option @37599060 is the one to go for, as it eliminates any adaptation required in my post @37599075

Be aware though that his substitute is removing row "1" from the address lable, you would need to adapt that substitution if your values were in different rows (example dragging it down several rows.....)

Are you interested in knowing the column and not the value? Then, may be this will help.

If the values are written from column B to column F. And we are writing the formula in column A

=MATCH(MAX(B1:F1),B1:F1)+COLUMN(B1)

Now, it will give you the column number for the column that has the maximum value. Now, you can use a index for "A", "B" .... to get the column Name.

Som

Hello Dave,

In fact the 1 is inserted in the formula in the ADDRESS function so it's always going to be a 1, e.g. for data anywhere in row 3 you can use

=SUBSTITUTE(ADDRESS(1,MATCH(MAX(3:3),3:3,0),4),1,"")

regards, barry

And now a macro solution. This function will give the columm headers (if they are cell entries) with the following UDF

=maxcol(B1:F1)

Function maxcol(r As Range)
Dim cel As Range, maxval As Double
For Each cel In r
If maxval < WorksheetFunction.Max(r.EntireColumn) Then
maxval = WorksheetFunction.Max(cel.EntireColumn)
maxcol = cel
End If
Next cel
End Function

if the A, B, C etc are column references then you can change

maxcol = cel

to

maxcol = cel.Address(False, False)

Ha, that's funny, I know what I meant, but I see where you are going, I meant something like this;

=SUBSTITUTE(ADDRESS(1,MATCH(MAX(A1:Z1),A1:Z1,0),4),1,"")
=SUBSTITUTE(ADDRESS(2,MATCH(MAX(A1:Z1),A1:Z1,0),4),2,"")

I just wasn't thinking (why would I even want to do this, as it doesn't matter).....doh!

This is going to tickle me for the rest of the day.

So let me get this correct.....

Assume row 1 has headers...

And if the value in column c is the greatest out of b,c,d and then they answer in column a is c and in the next row down if the value in column d is the greatest the in column a it's d..

And thanks for the feedback

If I read that right then I think you need my suggestion from way back, try this in A2 copied down

=INDEX(B$1:E$1,MATCH(MAX(B2:E2),B2:E2,0))

regards, barry

Many thanks to all the experts