Percentiles in Excel based on $ amounts

[waters130]

I am needing to determine the percentiles for a list of part #'s. I need to do this based on the $ amount of sales. I tried using the percentile function, but that bases it's calculation on the count of the items, not based on the total dollar amount. What I need to find out is what accounts for the bottom 20% of total sales dollars. How can I do this?

Thanks,

waters130

 

[SkipVought]

Hi,

Check out the [v]AutoFilter - Top 10[/b] feature.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[waters130]

Thanks so much that might work, but I am hoping for something a little different.

Let me explain a little more of what I am trying to accomplish. I am using this spreadsheet to calculate safety stock qtys for a distribution center. The bottom 20% get one zscore, the next 20% get another, and so on. This can change from month to month based on sales. I can do the filter and put in the zscore for each of these item, but I would prefer to find a formula that would make it more automatic.

Waters130

 

[AnotherHiggins]

->I am needing to determine the percentiles for a list of part #'s. I need to do this based on the $ amount of sales.

Please post a little example data so we can see your layout.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[waters130]

My spreadsheet currently has 26 weeks worth of sales information brought in through a pivot table with this format. There are of course 100's of part numbers. I have the column for the percentile. Previously I have manually calculated what the total dollar sales were and then figured which items make up each percentile of the sales. I then assign a zscore based on the percentile for those items to use in the safety stock calculation.

Everything I have found doesn't calculate off of the dollar amount, it does it off of the # of items. For example if I have 100 items and I choose to get the bottom 20th percentile, then it will choose 20 items, even though maybe 35 items make up the bottom 20% of sales.

Anyway here is a little example of how my data is stored.


Part# Week - 01 Week - 02 Week - 03 Total prcntle
123456 $1,000 $1,500 $1,200 $3,700 Need this
987456 $10,000 $8,600 $9,954 $28,554
456475 $9,876 $10,500 $9,500 $29,876
156478 $450 $600 $900 $1,950


Waters130

 

[SkipVought]

The way you have your data stored is a problem. Your data needs to be normalized, in order to be able to use the plethora of data analysis and data reporting tools in Excel.

Can you import the data WITHOUT using the PivotTAble Wizard? Maybe using MS Query?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[xlbo]

In this case, I don;t think the layout is the problem. The problem is with your interpretation of the percentile function.

The percentile function tells you, based on an array of numbers, what the threshold is for a certain percentile

In a blank cell away from your data (in say AD1), enter
=percentile(range_of_sales,0.2)

where range_of_sales is the range of cells containing your "Total Sales"

This will give you your threshold amount for the 20th percentile. Against your list fo sales, your formula is then

=if(AC2<=$AD$1,"Bottom 20","")

For more breakpoints, you can use a little table of percentiles e.g.

=percentile(range_of_sales,0.2)
=percentile(range_of_sales,0.4)
=percentile(range_of_sales,0.6)
=percentile(range_of_sales,0.8)

and then use a vlookup with the 4th argument set to TRUE to enable you to return a non exact match...


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question