Hey everyone. I have been stuck on this issue for a few days now. I have multiple criteria for the ranking, "State" and "Type". And I want it to be ranked based on the "Share". This is just a sample and actually have hundred of rows. I have figured it out so that it filters to the State and Type for the SUMPRODUCT(). But when I try to throw in a COUNTIF() I get crazy decimal places (see Group Rank (calc)).
Here is my formula. My desired result would be a rank with repeating values. Actually the decimals go out much further, and I can't symply "ROUND()" that I can figure out to resolve this issue.
Group Rank (calc) =SUMPRODUCT((--([@State]=[State])),(--([@[Line Of Business]]=[Line Of Business])),(--([@[Share]]<=[Share]))/COUNTIF([Share],[Share]))
Here is my formula. My desired result would be a rank with repeating values. Actually the decimals go out much further, and I can't symply "ROUND()" that I can figure out to resolve this issue.
Group Rank (calc) =SUMPRODUCT((--([@State]=[State])),(--([@[Line Of Business]]=[Line Of Business])),(--([@[Share]]<=[Share]))/COUNTIF([Share],[Share]))
State | Line of Business | Share | Group Rank (calc) |
IA | Homeowners | 2303 | 1.3 |
IA | Homeowners | 257 | 7.1 |
IA | Homeowners | 264 | 6.1 |
IA | Homeowners | 264 | 6.1 |
IA | Homeowners | 264 | 6.1 |
IA | Homeowners | 335 | 5.2 |
IA | Homeowners | 335 | 5.2 |
IA | Homeowners | 355 | 4.6 |
IA | Homeowners | 355 | 4.6 |
IA | Homeowners | 500 | 4.3 |
IA | Homeowners | 899 | 3.3 |
IA | Homeowners | 899 | 3.3 |
IA | Homeowners | 899 | 3.3 |
IA | Homeowners | 899 | 3.3 |
IA | Homeowners | 1162 | 2.3 |
IA | Homeowners | 1162 | 2.3 |
IA | Homeowners | 1162 | 2.3 |