Solved Max function in more than one cell.

October 1, 2014 at 06:37:58
Specs: Windows 7
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

See More: Max function in more than one cell.

Report •


✔ Best Answer
October 1, 2014 at 14:26:47
WHEN NO VALUES IN THE CELLS IT RETURNS #N/A

This 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

http://www.skeptic.com/

message edited by mmcconaghy



#1
October 1, 2014 at 07:12:27
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) 14

In 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

http://www.skeptic.com/


Report •

#2
October 1, 2014 at 09:45:36
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

Report •

#3
October 1, 2014 at 10:26:43
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 Leahy
 

In 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

http://www.skeptic.com/


Report •

Related Solutions

#4
October 1, 2014 at 11:24:54
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

Report •

#5
October 1, 2014 at 11:29:52
the function however it only shows a value as #N/A

That'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

http://www.skeptic.com/


Report •

#6
October 1, 2014 at 12:03:29
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

Report •

#7
October 1, 2014 at 12:08:31
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                  14

Enter 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

http://www.skeptic.com/


Report •

#8
October 1, 2014 at 12:43:16
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                 14

If there are multiple instances of the same MAX value this will return each name.

This is an ARRAY formula, and as such it MUST be entered
using the key combination of CTRL-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

http://www.skeptic.com/


Report •

#9
October 1, 2014 at 13:22:11
If there are multiple instances of the MAX value then message "No winners this week"

Report •

#10
October 1, 2014 at 13:33:47
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

http://www.skeptic.com/


Report •

#11
October 1, 2014 at 14:10:29
THAT WORKS BUT WHEN NO VALUES IN THE CELLS IT RETURNS #N/A

Report •

#12
October 1, 2014 at 14:26:47
✔ Best Answer
WHEN NO VALUES IN THE CELLS IT RETURNS #N/A

This 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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#13
October 1, 2014 at 14:48:46
All good now, thanks Mike and thank you for all your help and patience.
Chris

Report •


Ask Question