I have used the Max function to obtain the maximum value in a column, however if there is more than one cell with a maximum how can I return a value of 0

✔ Best Answer

WHEN NO VALUES IN THE CELLS IT RETURNS #N/AThis will return the "No Winners This Week" message, OK?

=IF(COUNTIF(AG1:AG10,MAX(AG1:AG10))<>1,"No Winners this Week",INDEX(C1:C10,MATCH(MAX(AG1:AG10),AG1:AG10,0)))

EDIT ADDED:This one is a bit longer and will simply blank out the cell

=IF(COUNT(AG1:AG10)=0,"",IF(COUNTIF($AG$1:$AG$10,MAX($AG$1:$AG$10))<>1,"No Winners this Week",INDEX($C$1:$C$10,MATCH(MAX($AG$1:$AG$10),$AG$1:$AG$10,0))))

MIKE

message edited by mmcconaghy

Try this, with your Data in Column A, cells 1 thru 10, like: A 1) 12 2) 74 3) 94 <-- 4) 83 5) 92 6) 31 7) 31 8) 94 <-- 9) 8 10) 14In cell A11 enter this formula:

=IF(COUNTIF(A1:A10,MAX(A1:A10))>1,0,MAX(A1:A10))

You should get a Zero, change one of the 94's to 92

and you will get the MAX value which is 94.

Is that what your looking for?MIKE

Thanks Mike, works fine.To follow on from here,In the spreadsheet, column A has a series of numbers in the cells. From the maximum value in that column i then wish to display the corresponding cell from column B, which is a list of names, in cell C1

With your data like: A B 1) 12 Lilian Delsignore 2) 74 Norris Kaminsky 3) 94 Shavonda Mccook 4) 83 Sharmaine Nardone 5) 92 Raven Hendon 6) 31 Wilfredo Hardiman 7) 31 Nenita Raffaele 8) 96 Soila Thrower 9) 8 Claude Hubbell 10) 14 Margo LeahyIn cell C1 enter the formula:

=IF(COUNTIF(A1:A10,MAX(A1:A10))=1,VLOOKUP(MAX(A1:A10),A1:B10,2,0),"Multi Max Value")

You should get the name: Soila Thrower

You did not specify what to do if there were multiple instances of the MAX value so,

if there is more than one instance of the MAX value,

you get the message "Multi Max Value".

Change this to suit your preference.MIKE

That works however to simplify matters i stated columns A and B in my initial question but in my actual spreadsheet column AG contains the numbers and column C the names with the columns in between containing various values. I changed the relative cells in the function however it only shows a value as #N/A

the function however it only shows a value as #N/AThat's because the VLOOKUP() function ONLY get data that is to it's RIGHT

So you can not have the Numbers in AG

and get the name from Column C

using a VLOOKUP()If you had given that information in your original post,

it would have made life so much simpler.If you can swap the columns, then the formulas should work.

MIKE

I cant change the columns around and I don't have sufficient knowledge to know exactly what information you need but its ok, I'll work through it. Thanks anyway

Try this, with your data like: C AG 1) Lilian Delsignore 12 2) Norris Kaminsky 74 3) Shavonda Mccook 94 4) Sharmaine Nardone 83 5) Raven Hendon 92 6) Wilfredo Hardiman 31 7) Nenita Raffaele 31 8) Soila Thrower 96 9) Claude Hubbell 8 10) Margo Leahy 14Enter this formula:

=INDEX(C1:C10,MATCH(MAX(AG1:AG10),AG1:AG10,0))

You should get the name: Soila Thrower

You still have not not specifed what to do if there were multiple instances of the MAX value?

With this formula if there are multiple instances, you will only get the First Instance.

MIKE

This is a much more robust formula, so with your data like this: C AG 1) Lilian Delsignore 12 2) Norris Kaminsky 74 3) Shavonda Mccook 96 <-- 4) Sharmaine Nardone 83 5) Raven Hendon 92 6) Wilfredo Hardiman 31 7) Nenita Raffaele 31 8) Soila Thrower 96 <-- 9) Claude Hubbell 8 10) Margo Leahy 14If there are multiple instances of the same MAX value this will return each name.

This is an ARRAY formula, and as such it

be enteredMUST

using the key combination ofCTRL-SHIFT-ENTER.=IFERROR(INDEX($C$1:$C$10,SMALL(IF(MAX($AG$1:$AG$10)=$AG$1:$AG$10,ROW($AG$1:$AG$10),""),ROW())),"")

After you enter the formula, using CTRL-SHIFT-ENTER the formula should appear with

curely brackets around it like this:{=IFERROR(INDEX($C$1:$C$10,SMALL(IF(MAX($AG$1:$AG$10)=$AG$1:$AG$10,ROW($AG$1:$AG$10),""),ROW())),"")}

You DO NOT put them there, Excel will do it if you have entered it correctly.

Once you have entered the formula, drag it down several rows,

to accommodate each new name that is matched.You should have the two names:

Shavonda Mccook

&

Soila Thrower

each in it's own cell.See how that works for you.

MIKE

If there are multiple instances of the MAX value then message "No winners this week"

Try this one: =IF(COUNTIF(AG1:AG10,MAX(AG1:AG10))>1,"No Winners this Week",INDEX(C1:C10,MATCH(MAX(AG1:AG10),AG1:AG10,0)))

MIKE

THAT WORKS BUT WHEN NO VALUES IN THE CELLS IT RETURNS #N/A

WHEN NO VALUES IN THE CELLS IT RETURNS #N/AThis will return the "No Winners This Week" message, OK?

=IF(COUNTIF(AG1:AG10,MAX(AG1:AG10))<>1,"No Winners this Week",INDEX(C1:C10,MATCH(MAX(AG1:AG10),AG1:AG10,0)))

EDIT ADDED:This one is a bit longer and will simply blank out the cell

=IF(COUNT(AG1:AG10)=0,"",IF(COUNTIF($AG$1:$AG$10,MAX($AG$1:$AG$10))<>1,"No Winners this Week",INDEX($C$1:$C$10,MATCH(MAX($AG$1:$AG$10),$AG$1:$AG$10,0))))

MIKE

message edited by mmcconaghy

All good now, thanks Mike and thank you for all your help and patience.

Chris

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History