What is the cell that Max is in? 2

[jmarkus]

This seems simple. I can find the maximum value in a range of cells by using the max command. How can I tell what cell returned the value that was the maximum?

I'm looking for a non-VBA (function/formula only) solution.

Thanks,
Jeff

 

[palusa]

Hi Jeff,

did you try something like:

=+MATCH(+MAX(A4:F4);A4:F4;0)

where A4:F4 is the range containing your values?

It should return the offset of the cell containing the max value.

Hope it helps.

_LF

 

[brengine]

If you are looking for a visual queue, you can Conditionally Format all the cells in the range such that if their value equals the MAX value in the range the cell will highlight (or bold text or whatever you prefer).

Ken

 

[peglor]

Use Format --> conditional formatting to check for cells that equal the max value (Which you already have in a known cell).

Procedure is: Select the column/cells you want to search
Select Format --> conditional formatting, setting the condition to 'equals' and pick the cell with the =max(Range) formula in it as the target for the cell to equal. Set a format for the text such as bold/different colour/highlighted etc. and click OK.

The max value in your list of cells will now be reformatted to match the new settings you've given it.

 

[jmarkus]

Thanks all.

I tried the MATCH concept and it does what I want.

Jeff

 

[melone]

But how do you return the actual cell reference, i.e. A34?

 

[peglor]

You should be able to get Excel to tell you what cell to look for by building the cell reference from the offset value using the formula =C3&C4, where C3 is the column letter and C4 is the row number. I can't use this in further formulae as anything more than text though :-(

I get an error in office 2000 using the match function for more than a single column of data too. Also, on reading the help file and checking on a spreadsheet, be aware that the match function only flags the first value in a list equal to teh max value. Conditional format flags all values (Though in a less useful way).

 

[bltseattle]

To answer Melone's query,

If the range of cells is say B5:B18, to find the address of the cell with the max use the following expression:

=CELL("address",OFFSET(B4,MATCH(MAX(B5:B18),B5:B18,0),0))

This will return the first instance of the maximum value; I don't know how you could possibly return more than one address (for 2 or more of the same max values) unless you use a VBA approach.