Percentiles for two columns of data/weighted percentile?

[grapeguy]

Hi,
First post here. I found the board by googling various problems and this board came up a lot.

I'm using Excel 2010.
Here's my problem:

I have two sets of data. Column B is the price per ton paid for grapes in a year. Column C is the number of tons for each price paid.
For example:

B C
$1000 4000
$1200 5500
$1400 4500
$2000 3400
$3000 1000
$3001 500
$3002 400
$3003 100

I know it's no problem to us PERCENTILE for a single column, but these results would be meaningless because the 90th percentile of price doesn't reflect the market. Far more tons were bought at a lower price.

So, how do I come up with a price percentile that is weighted by tons? Does this involved SUMPRODUCT in some way or do I have to get more complicated with AGGREGATE and such?

Thanks for any help on this.

 

[Dendro]

Hi grapeguy,

If you don't mind creating an extra column with the percentages as the below table:

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]$1000
[/TD]
[TD="width: 64, align: right"]4000[/TD]
[TD="width: 64, align: right"]0,206186[/TD]
[/TR]
[TR]
[TD]$1200[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]0,489691[/TD]
[/TR]
[TR]
[TD]$1400[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]0,721649[/TD]
[/TR]
[TR]
[TD]$2000[/TD]
[TD="align: right"]3400[/TD]
[TD="align: right"]0,896907[/TD]
[/TR]
[TR]
[TD]$3000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]0,948454
[/TD]
[/TR]
[TR]
[TD]$3001[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0,974227[/TD]
[/TR]
[TR]
[TD]$3002[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0,994845[/TD]
[/TR]
[TR]
[TD]$3003[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

Now you have the percentage of tons bought per price. If you would use the following formula:

=IF(SEARCH(0,9;C5:C12)<0,9;SEARCH(0,9;C5:C12;A6:A13);SEARCH(0,9;C5:C12;A5:A12))

in a different cell, this would result in the 90th percentile, being 3000$ (90% of the tons bought are bought at 3000$ or less).

Note: my table (above) starts at A5 and ends in C12.

The formula in C5 to calculate percentages is: =SUM($B$5:B5)/SUM($B$5:$B$12)
Copy this down until C12
The 90th percentile result cell would hold the following formula (same as the first formula given, to clarify the references used in this formula:
=IF(SEARCH(0,9;C5:C12)<0,9;SEARCH(0,9;C5:C12;A6:A13);SEARCH(0,9;C5:C12;A5:A12))