I'm sorry in advanced if my explanation isn't very clear, but here goes!
I've got a grid of results from four teams who are scored on a weekly basis. So far, my calculation has the following which highlights how many times each team has won (listed at the top of each team):
(Not really relevant, but there you go...)
What I'm trying to do is to record the highest streak each team has had as the winner for the week. So the table looks something like this (the numbers refer to position, not score):
<tbody>
</tbody>
In theory, Team A should have 2 and Team B should have 3. The formula I have so far is:
However, I know this is wrong because it's only returning 1 and not 3. I can't use a definite value such as '1' because the values are below 0 (a percentage) and vary each time. Can anyone help with this please?
I've got a grid of results from four teams who are scored on a weekly basis. So far, my calculation has the following which highlights how many times each team has won (listed at the top of each team):
Code:
=SUMPRODUCT(IF(A3:A58>0,(SUBTOTAL(4,OFFSET($A$3:$D$58,ROW($A$3:$D$58)-ROW(A$3),0,1))=A3:A58)+0))(Not really relevant, but there you go...)
What I'm trying to do is to record the highest streak each team has had as the winner for the week. So the table looks something like this (the numbers refer to position, not score):
| A | B | C | D |
| 1 | 2 | 3 | 4 |
| 4 | 3 | 2 | 1 |
| 1 | 2 | 4 | 3 |
| 1 | 3 | 2 | 4 |
| 4 | 1 | 2 | 3 |
| 2 | 1 | 3 | 4 |
| 2 | 1 | 4 | 3 |
<tbody>
</tbody>
In theory, Team A should have 2 and Team B should have 3. The formula I have so far is:
Code:
=MAX(FREQUENCY(IF(A3:A53=MAX(A3:D53),ROW(A3:D53)),IF(A3:A53=0,ROW(A3:D53))))However, I know this is wrong because it's only returning 1 and not 3. I can't use a definite value such as '1' because the values are below 0 (a percentage) and vary each time. Can anyone help with this please?
