aefitzgerald
New Member
- Joined
- Jan 17, 2018
- Messages
- 8
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 === 250 === $1.25 === 250
Orange === 50 === $1.75 === 300
I am trying to come up with a formula that will allow me to quickly calculate the 75th percentile price for every item in a list, like this:
A === B
Item === 75th percentile price
Apple === $2.75
Banana === $2.00
Orange === $1.25
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 several items at once?
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 === 250 === $1.25 === 250
Orange === 50 === $1.75 === 300
I am trying to come up with a formula that will allow me to quickly calculate the 75th percentile price for every item in a list, like this:
A === B
Item === 75th percentile price
Apple === $2.75
Banana === $2.00
Orange === $1.25
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 several items at once?