Using SumProduct and Count To Calculate % Ranks

What formula should I use in Column C to return the % figures in Column C?

This formula in Column C should rank in percent the numerical rank in Column
B for Each Label indicated in Column A.

For example, in the first row below, it shows that 4 in Col B is in the top
57% of the 7 Xs in Col A. The manual formula is =4/7.

Could you also explain generally what the sumproduct formula that should be
used with this is doing in English? Thanks a lot!

Col A Col B Col B
X 4 57%
X 7 100%
X 2 29%
X 1 14%
X 5 71%
X 6 86%
X 3 43%
XX 4 100%
XX 2 50%
XX 1 25%
XX 3 75%
XXX 1 14%
XXX 3 43%
XXX 6 86%
XXX 4 57%
XXX 7 100%
XXX 2 29%
XXX 5 71%
XXXX 2 50%
XXXX 3 75%
XXXX 4 100%
XXXX 1 25%

You don't need SUMPRODUCT

If these values start in row 2 and go down to row 25, then in col C use

=B2/COUNTIF($A$2:$A$25,a2)

"SteveC" wrote:

> What formula should I use in Column C to return the % figures in Column C?
>
> This formula in Column C should rank in percent the numerical rank in Column
> B for Each Label indicated in Column A.
>
> For example, in the first row below, it shows that 4 in Col B is in the top
> 57% of the 7 Xs in Col A. The manual formula is =4/7.
>
> Could you also explain generally what the sumproduct formula that should be
> used with this is doing in English? Thanks a lot!
>
> Col A Col B Col B
> X 4 57%
> X 7 100%
> X 2 29%
> X 1 14%
> X 5 71%
> X 6 86%
> X 3 43%
> XX 4 100%
> XX 2 50%
> XX 1 25%
> XX 3 75%
> XXX 1 14%
> XXX 3 43%
> XXX 6 86%
> XXX 4 57%
> XXX 7 100%
> XXX 2 29%
> XXX 5 71%
> XXXX 2 50%
> XXXX 3 75%
> XXXX 4 100%
> XXXX 1 25%
>
>

Hi Steve,

Try:
=B1/COUNTIF(A:A,A1)
in C1 and copy down as far as needed. I can't see any need for a SUMPRODUCT
solution.

For an explanation of the COUNTIF function, input the formula and click on
the 'Paste function' icon on the toolbar.

Cheers

"SteveC" <[email protected]> wrote in message
news:[email protected]...
> What formula should I use in Column C to return the % figures in Column C?
>
> This formula in Column C should rank in percent the numerical rank in
Column
> B for Each Label indicated in Column A.
>
> For example, in the first row below, it shows that 4 in Col B is in the
top
> 57% of the 7 Xs in Col A. The manual formula is =4/7.
>
> Could you also explain generally what the sumproduct formula that should
be
> used with this is doing in English? Thanks a lot!
>
> Col A Col B Col B
> X 4 57%
> X 7 100%
> X 2 29%
> X 1 14%
> X 5 71%
> X 6 86%
> X 3 43%
> XX 4 100%
> XX 2 50%
> XX 1 25%
> XX 3 75%
> XXX 1 14%
> XXX 3 43%
> XXX 6 86%
> XXX 4 57%
> XXX 7 100%
> XXX 2 29%
> XXX 5 71%
> XXXX 2 50%
> XXXX 3 75%
> XXXX 4 100%
> XXXX 1 25%
>
>

Thank you very much!

Great! Thanks.