What is the cell that Max is in?
What is the cell that Max is in?
(OP)
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
I'm looking for a non-VBA (function/formula only) solution.
Thanks,
Jeff





RE: What is the cell that Max is in?
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
RE: What is the cell that Max is in?
Ken
RE: What is the cell that Max is in?
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.
RE: What is the cell that Max is in?
I tried the MATCH concept and it does what I want.
Jeff
RE: What is the cell that Max is in?
RE: What is the cell that Max is in?
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).
RE: What is the cell that Max is in?
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.