Excel: Counting the number of times the maximum value of a row falls within a specifed column

[hornsfan01]

To me, this is fairly complicated and I may have bitten off more than I can chew. But I've been searching on the internet and trying things in Excel for two days with no real success, so here goes:

I have a table of numerical values with a row of text headers at the top.

What I'd like to be able to do is to count the number of times the maximum value within each row falls within a specified column, with a hierarchy of column headers specified as "tiebreakers."
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Tiebreaker order:
[/TD]
[TD]No. of max values:
[/TD]
[TD="align: center"]Red
[/TD]
[TD="align: center"]Yellow
[/TD]
[TD="align: center"]Green
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Red
[/TD]
[TD][/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Green
[/TD]
[TD][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]Yellow
[/TD]
[TD][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]3
[/TD]
[/TR]
</TBODY>[/TABLE]


The order of the column headers specified in A2:A4 is a hierarchy by which I would like to break ties for max value. I'd like to put the answers in B2:B4.

So, basically in B2, I'd like the formula to say, "Does the max value for each row of C2:E6 fall in the column whose column header equals the text in A2? If so, count=count+1. Once all rows in C2:E6 have been examined for this question, put the final count in B2."

In B3, I'd like the formula to say, "Does the max value for each row of C2:E6 fall in the column whose column header equals the text in A3? If so, then as long as the max value does not ALSO fall in the column whose header matches the text in A2, count=count+1." Final count gets put in B3.

In B4, I'd like the formula to say, "Does the max value for each row of C2:E6 fall into the column whose header equals the text in A4? If so, then as long as the max value does not also fall in either the column whose header matches A2 or A3, then count=count+1." Final count gets put in B4.

The reason I specify it like this is that, while the cells A1, B1, the answers in B2-B4, and the entire table from C1-E6 will remain static, the order of the cells in A2-A4 may be changed by the user to specify a different order of tiebreaker. So the formulas would have to look at order of tiebreaker to see whether to increment count.

So if A2:A4 was Red|Green|Yellow in that order, I'd like to end up in B2:B4 with 3 | 1 | 1
B2 gets incremented for C2:E2, C3:E3 because it shares the max value and is highest on the tiebreaker list, and C4:E4 because it shares the max value and is highest on the tiebreaker list.
B3 would be incremented for C6:E6 only.
B4 would be incremented for C5:E5 only.


Once I have those values, I'd like to then be able to go in and type, for example, Green | Yellow | Red in cells A2:A4.
I'd like to have B2:B4 recalculate and give the following values:

[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Tiebreaker Order
[/TD]
[TD]No. of max values:
[/TD]
[TD="align: center"]Red
[/TD]
[TD="align: center"]Yellow
[/TD]
[TD="align: center"]Green
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Green
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Yellow
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]Red
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]3
[/TD]
[/TR]
</TBODY>[/TABLE]

B2 gets incremented for C3:E3, C4:E4, C5:E5, and C6:E6. Yellow does not hold any max values that Green doesn't also hold, so it stays at zero. Red gets incremented for C2:E2.


Thanks ahead of time to anyone who takes a look at this and can let me know if this is even possible.

 

[Marcelo Branco]

Hi,

Shoudn't the results for A2:A4 = Red, Green, Yellow be 3, 2, 0?

Max values for each row = 6, 4, 1, 4, 3
A2 (Red) = True, True, True, False, False --> count = 3
A3 (Green) = False, False (because A2 = True), False( A2=True), True , True --> count =2
A4 (Yellow) = False, False, False (A2 = True), False, False (A3=True) --> count = 0

Could you clarify?

M.

 

[hornsfan01]

Whoops, you're right. 3,2,0. As I made the example, I accidentally read the values as R/Y/G, not R/G/Y as specidied in the hierarchy. I'll correct it.

ADDENDUM: Well, the gray bar on the bottom of the post doesn't give me an option to edit the original post anymore. I can edit this one but not the top one.

But yes, it should have been 3,2,0. If A2:A4 had been Red / Yellow / Green, then it would have been 3 / 1 / 1 in cells B2:B4.

 

[Marcelo Branco]

WHoops, you're right. 3,2,0. As I made the example, I accidentally read the values as R/Y/G, not R/G/Y as specidied in the hierarchy. I'll correct it.

Let me ask: in your real case (real data) there are only 3 colors? I'm asking because a solution with many colors can be very complicated with formulas (as far as i know...)

M.

 

[barry houdini]

One way would be to use a "helper column". In F2 use this "array formula"

=INDEX(A$2:A$4,MATCH(TRUE,ISNUMBER(MATCH(A$2:A$4,IF(MAX(C2:E2)=C2:E2,C$1:E$1),0)),0))

confirm with CTRL+SHIFT+ENTER and copy down to F6

Now in B2 you just need a COUNTIF formula to count results in column F, i.e. in B2 copied down

=COUNTIF(F:F,A2)

If you change the order of A2:A4 then the results will change, I get 3-2-0 and 4-0-1 for your examples. You can easily extend that solution to any number of rows or columns.....

 

[hornsfan01]

Yes, there are only three colors and that will not change.

 

[hornsfan01]

One way would be to use a "helper column". In F2 use this "array formula"

=INDEX(A$2:A$4,MATCH(TRUE,ISNUMBER(MATCH(A$2:A$4,IF(MAX(C2:E2)=C2:E2,C$1:E$1),0)),0))

confirm with CTRL+SHIFT+ENTER and copy down to F6

Now in B2 you just need a COUNTIF formula to count results in column F, i.e. in B2 copied down

=COUNTIF(F:F,A2)

If you change the order of A2:A4 then the results will change, I get 3-2-0 and 4-0-1 for your examples. You can easily extend that solution to any number of rows or columns.....

Amazing, looks like it works perfectly. I'm going to go through the evaluate formula process to see if I can understand what you came up with and what I can learn from it for next time. Thanks to you for the solution and thanks to Marcelo for taking a look at it, too. I appreciate it IMMENSELY.

 

[Marcelo Branco]

One way would be to use a "helper column". In F2 use this "array formula"

=INDEX(A$2:A$4,MATCH(TRUE,ISNUMBER(MATCH(A$2:A$4,IF(MAX(C2:E2)=C2:E2,C$1:E$1),0)),0))

confirm with CTRL+SHIFT+ENTER and copy down to F6

Now in B2 you just need a COUNTIF formula to count results in column F, i.e. in B2 copied down

=COUNTIF(F:F,A2)

If you change the order of A2:A4 then the results will change, I get 3-2-0 and 4-0-1 for your examples. You can easily extend that solution to any number of rows or columns.....

Very nice!!!

M.

 

[RQH]

Wow~~~~~~This formula is awesome Barry. But could you please explain the logic of first embedded 'Match' formula
Match(True, is number........)

I only know the first thing to write in match formula is array. How come you used 'TRUE'?

Thanks
Reichi

 

[RQH]

This is really smart, Barry!
Could you explain your first embedded Match formula?
How come you used 'true' rather than any array for your first Match formula. Could you explain the logic behind?

Thanks
Reichi!

One way would be to use a "helper column". In F2 use this "array formula"

=INDEX(A$2:A$4,MATCH(TRUE,ISNUMBER(MATCH(A$2:A$4,IF(MAX(C2:E2)=C2:E2,C$1:E$1),0)),0))

confirm with CTRL+SHIFT+ENTER and copy down to F6

Now in B2 you just need a COUNTIF formula to count results in column F, i.e. in B2 copied down

=COUNTIF(F:F,A2)

If you change the order of A2:A4 then the results will change, I get 3-2-0 and 4-0-1 for your examples. You can easily extend that solution to any number of rows or columns.....