Finding top 20% of spend dollars in a column

  • Thread starter Thread starter wjs81866
  • Start date Start date
W

wjs81866

I can't remember the formula to find the top 20% of the dollars spent in a
column.

I have 692 rows with dollar amounts I need to report the % (rank) compared
to everything else in the column.
 
Say we have this data in a column:

75
46
59
75
32
62
98
14
46
68
49
92
22
28
49
61
40
3
8
21

first copy the column to column B and sort it descending

In C1 enter:
=B1/SUM(B:B)

In C2 enter:
=SUM($B$1:B2)/SUM(B:B) and copy down.

We now see:

75 98 10.34%
46 92 20.04%
59 75 27.95%
75 75 35.86%
32 68 43.04%
62 62 49.58%
98 61 56.01%
14 59 62.24%
46 49 67.41%
68 49 72.57%
49 46 77.43%
92 46 82.28%
22 40 86.50%
28 32 89.87%
49 28 92.83%
61 22 95.15%
40 21 97.36%
3 14 98.84%
8 8 99.68%
21 3 100.00%

It is easy to see that the top 20% is the first two items. They account for
20% of the total.
 
Maybe try RANK, PERCENTRANK

Assume the dollar amounts in A1:A692
In B1: =RANK(A1,$A$1:$A$692)
Copy down

In C1: =PERCENTRANK($A$1:$A$692,A1)
Format C1 as percentage, copy down
Returns relative rankings of the dollar amounts as percentages (0% - 100%,
lowest - highest) for easier visuals. Spends of >=80% will be your top 20%.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
 
Back
Top