Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a range of cells (B1:B5) that contain random reading values. I want to find the maximum value in that range, and display 'Max' (or a defined number) in the next column, on the same row that the maximum value is located. For example:

B1 = 2
B2 = 4
B3 = 6    C3 = 'Max'
B4 = 3
B5 = 1

The maximum value '6' is found in cell B3. I want 'Max' (or a specific number) to be displayed only in cell C3.

share|improve this question

You can do this by using two functions in concert together.

The Max function and the if statement. Insert this in column next to your data:

=IF(MAX(B$1:B$5) = B1, "Max", "")

This will first check for the max value in the list of values you have. Then it will check to see if the value in that column matches the max value. If it does match, then it will print out Max. If it doesn't, it'll be blank.

And of course drag the corner of the cell to copy the code in the cells below.

share|improve this answer
Perfect... I was trying to make it more complicated than it needed to be. I had just thought of trying it this way when you posted. Thanks. – Eric Dec 6 '11 at 21:29
BTW: If this answers your question, you should select it as such. – Rachel Hettinger Dec 7 '11 at 2:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.