Compare cells to determine which has the highest value

**ravichander** · 02-04-2012, 02:49 PM

Dear all:

Here is what I hope to accomplish, but having difficulty with:

1. Check cells C26, E26, G26, I26,K26, M26, 026, Q26 and S26
2. Determine which of the above have the highest value
3. Once identified, go the the corresponding cell in Row 4
4. Copy the style name
5. Paste in Cell B28
6. Repeat the process to determine the next highest number, repeat 3 and 4 above. Paste in Cell B29.

Thank you in advance. Spreadsheet attached.

- Ravi

**Pepe Le Mokko** · 02-04-2012, 03:05 PM

What should happen when two or more values in row 36 are equal?

**jeffreybrown** · 02-04-2012, 03:06 PM

How about this in cell B28 copied down to B29, but what about a duplicate number?

=INDEX($C$4:$S$4,MATCH(LARGE(IF(MOD($C$26:$S$26,1)=0,$C$26:$S$26),ROW(A1)),$C$26:$S$26,0))

This is a CSE formula so enter with Ctrl + Shift + Enter

**ravichander** · 02-04-2012, 03:43 PM

Hi Jeff:

Thank you for your kind response.

If there were duplicate numbers, then paste them in C29 and C30? Then it would be left to the readers' interpretation. Thanks.

- Ravi

**ravichander** · 02-05-2012, 02:38 PM

Hi Jeff:

The code you provided worked well. I created a situation where there are two identical values. The code only picked up the first instance of the high value. Can the code you provided be modified to handle this situation too? Thanks.

**daddylonglegs** · 02-05-2012, 04:54 PM

Try this "array formula" in B28

=IFERROR(INDEX($C$4:$S$4,MATCH(1,($C$26:$S$26= MAX(IF($C$4:$S$4<>"Pct.",$C$26:$S$26)))*($C$4:$S$4<>"Pct.")*(COUNTIF(B$27:B27,$C$4:$S$4)=0),0)),"")

Confirmed with CTRL+SHIFT+ENTER and copied down as far as required

**ravichander** · 02-05-2012, 05:51 PM

Originally Posted by daddylonglegs

Try this "array formula" in B28

=IFERROR(INDEX($C$4:$S$4,MATCH(1,($C$26:$S$26= MAX(IF($C$4:$S$4<>"Pct.",$C$26:$S$26)))*($C$4:$S$4<>"Pct.")*(COUNTIF(B$27:B27,$C$4:$S$4)=0),0)),"")

Confirmed with CTRL+SHIFT+ENTER and copied down as far as required

Your suggestion works when there were more than one cell in C26:S26 having the same value. I copied the formula into two cells below in column B. I also created a scenario where there were 3 cells in the given range with the same value. Here, B28 displayed the first style, B29 displayed the second style with the same value, and B30 displayed the third style with the same value.

However, in a scenario wherein one cell in the range has the highest value, followed by two other cells having a lower value but are identical, the formula in B28 displayed the style with the highest number, while B29 and B30 displayed nothing. If this can be fixed, then one part of the problem will be resolved.

The second part is to find out which cell has the next highest value and then display that style in D28 or E28, covering the situation of dealing with identical values too.

Your response is very much appreciated. Thanks.

**daddylonglegs** · 02-05-2012, 07:14 PM

OK, small change of approach - I'm not sure if you want to see the scores too but this will make it simpler. To get the top 2 scores (plus any ties) put this formula in B28

=IF(LARGE(IF(C$4:S$4<>"Pct.",C$26:S$26),ROWS(C$28:C28))>=LARGE(IF(C$4:S$4<>"Pct.",C$26:S$26),2),LARG E(IF(C$4:S$4<>"Pct.",C$26:S$26),ROWS(C$28:C28)),"")

confirm with CTRL+SHIFT+ENTER and copy down

and in C28 for the Styles

=IF(B28="","",INDEX(C$4:S$4,SMALL(IF(C$26:S$26=B28,IF(C$4:S$4<>"Pct.",COLUMN(C$26:S$26)-COLUMN(C$26)+1)),COUNTIF(B$28:B28,B28))))

also confirmed with CTRL+SHIFT+ENTER

see attached example

**snb** · 02-08-2012, 09:16 AM

or ?
In B32

PHP Code:


{=MAX(($C$4:$S$4<>"Pct.")*($C$26:$S$26))}

and in C32

PHP Code:


{=INDEX($C$4:$S$4;;MATCH($B32;$C$26:$S$26;0))}

in B33

PHP Code:


{=LARGE(($C$4:$S$4<>"Pct.")*($C$26:$S$26);2)}

in C33

PHP Code:


{=INDEX($C$4:$S$4;;MATCH($B33;$C$26:$S$26;0))}

**ravichander** · 02-09-2012, 05:28 PM

This code works. Thanks very much for your help.

**ravichander** · 02-09-2012, 05:30 PM

Dear SNB:

Thank you for your response. I copied the code into cell B32, but don't know if there is something I have to do in addition for that code to execute e.g., as in a CSE formula. I have no clue about what a PHP code is and how to get it to work. Thanks.

- Ravi