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. |
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. |
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. |
All times are GMT +1. The time now is 06:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com