Formula that searches for item name and calculates 75th percentile of price for all quantity?

[aefitzgerald]

Hello,

I am trying to pull data from another large spreadsheet (thousands of rows) which is currently sorted to look something like this;

A === B === C === D
Item === Quantity === Price === Running Total Qty.
Apple === 50 === $2.00 === 50
Apple === 100 === $2.25 === 150
Apple === 75 === $2.75 === 225
Banana === 200 === $1.75 === 200
Banana === 300 === $2.00 === 500
Orange === 100 === $0.50 === 100
Orange === 100 === $1.00 === 200
Orange === 50 === $1.25 === 250
Orange === 250 === $1.75 ===500


I am trying to come up with a formula that will allow me to quickly calculate the 75th percentile price for every item AND that takes the quantity into account. E.g. The 75th percentile for Oranges should come out to $1.75 because the 75th percentile of the quantity is sold at that price. I want to quickly populate the B column in a table like the one below (but with thousands of items).


A === B
Item === 75th percentile price
Apple ===
Banana ===
Orange ===


I am able to calculate the 75th percentile for one particular item name in the large spreadsheet by using a formula like this: LOOKUP(SUM(B2:B4)*0.75,D2:D4,C2:C4). However, I am not sure how to search for the item name in another spreadsheet AND calculate the 75th percentile. How can I quickly perform this calculation for many items at once?

 

[aefitzgerald]

Ah yes, that is what I am wanting to do! Thank you!