# Display a value next to a cell containing a maximum value in a range.. Excel 2007

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.

-

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.

-
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