×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

What is the cell that Max is in?
2

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

RE: What is the cell that Max is in?

2
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

RE: What is the cell that Max is in?

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

RE: What is the cell that Max is in?

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.

RE: What is the cell that Max is in?

(OP)
Thanks all.  

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

Jeff

RE: What is the cell that Max is in?

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

RE: What is the cell that Max is in?

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).

RE: What is the cell that Max is in?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close