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:

$1000	4000	0,206186
$1200	5500	0,489691
$1400	4500	0,721649
$2000	3400	0,896907
$3000	1000	0,948454
$3001	500	0,974227
$3002	400	0,994845
$3003	100	1

<tbody>
</tbody>

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))