| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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% |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using SumProduct and Count To Calculate % Ranks
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% |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using SumProduct and Count To Calculate % Ranks
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" wrote in message ... 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% |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using SumProduct and Count To Calculate % Ranks
Thank you very much!
|
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using SumProduct and Count To Calculate % Ranks
Great! Thanks.
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria | Excel Worksheet Functions | |||
| sumproduct partial text count | Excel Worksheet Functions | |||
| How to count uniques of a SUMPRODUCT subset? | Excel Worksheet Functions | |||
| Using sumproduct to count number by date | Excel Worksheet Functions | |||
| SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Linear Mode
