Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding top 20% of spend dollars in a column
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding top 20% of spend dollars in a 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. -- Gary''s Student - gsnu200829 "wjs81866" wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding top 20% of spend dollars in a column
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 --- "wjs81866" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percent and dollars in Same Column | Excel Discussion (Misc queries) | |||
Converting currency from US Dollar into Canadian dollars from column A to Column B | Excel Worksheet Functions | |||
How do I estimate the year spend if spend is $26000 as of May? | Excel Worksheet Functions | |||
How do I sum dollars in one column based on dates in another? | New Users to Excel | |||
I wish to make a formula that adds dollars in 1 column if a date . | Excel Worksheet Functions |