Hi Excel friends,
Looking to solve this problem without using an array formula:
Basically I want a formula to give me Conditional Rank of value based on name without duplicates
The best i can come up with is =SUMPRODUCT(($A$2:$A$12=A2)*(B2>=$B$2:$B$12)/COUNTIFS($B$2:$B$12,$B$2:$B$12))
The values it returns are in order but not integers. I'm stumped on this one.
Even better would be a formula that could allow me to use columns rather than ranges, and accommodate blanks.
Thanks Excel friends
Looking to solve this problem without using an array formula:
name | value | Conditional Rank (formula required to solve for this column) |
a | 2 | 1 |
a | 2 | 1 |
a | 7 | 3 |
a | 5 | 2 |
a | 5 | 2 |
a | 15 | 4 |
b | 15 | 3 |
b | 15 | 3 |
b | 17 | 4 |
b | 2 | 1 |
b | 11 | 2 |
Basically I want a formula to give me Conditional Rank of value based on name without duplicates
The best i can come up with is =SUMPRODUCT(($A$2:$A$12=A2)*(B2>=$B$2:$B$12)/COUNTIFS($B$2:$B$12,$B$2:$B$12))
The values it returns are in order but not integers. I'm stumped on this one.
Even better would be a formula that could allow me to use columns rather than ranges, and accommodate blanks.
Thanks Excel friends