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?

 

[Gerald Higgins]

Looks like a duplicate of this post . . .
https://www.mrexcel.com/forum/excel...-percentile-another-variable.html#post4989003

 

[sheetspread]

Does only one price per group equal or exceed the threshold? If so:

Excel 2010
	A	B	C	D	E	F
1	Item	Quantity	Price	Running Total Qty.	75th %ile
2	Apple	50	$2.00	50	87.5
3	Apple	100	$2.25	150	87.5	2.25
4	Apple	75	$2.75	225	87.5
5	Banana	200	$1.75	200	275
6	Banana	300	$2.00	500	275	2
7	Orange	100	$0.50	100	137.5
8	Orange	100	$1.00	200	137.5
9	Orange	50	$1.25	250	137.5
10	Orange	250	$1.75	500	137.5	1.75
Sheet7

Cell Formulas
Range	Formula
F2	=IF(B2>=E2,C2,"")
E2	{=PERCENTILE(IF($A$2:$A$10=A2,$B$2:$B$10),0.75)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Then you can filter, lookup, etc.

 

[aefitzgerald]

I see what you are trying to do there. Unfortunately, it looks like there might be a problem because the result for apples is wrong - it should be $2.75. Any idea why that error is coming up?

Does only one price per group equal or exceed the threshold?

I believe so.

 

[aefitzgerald]

I just tried using those formulas in my spreadsheet. For whatever reason, the F2 formula only produces zeros for every cell in that column...

I am still unable to figure out how to do this.

 

[sheetspread]

the result for apples is wrong - it should be $2.75

Why? isn't it below the the 75th %ile quantity (87.5)? Or are we comparing the running total instead of the standalone quantity?

 

[aefitzgerald]

Why? isn't it below the the 75th %ile quantity (87.5)? Or are we comparing the running total instead of the standalone quantity?

The 75th percentile for the quantity of apples (225 apples) is 168.75. The price of the apples is less than $2.75 for the first 150 apples and $2.75 for the 151st through 250th apples. So the 75th percentile falls in the group with price of $2.75.


What do the numbers in the E column (87.5, 275, 137.5) represent?

 

[sheetspread]

=PERCENTILE({50;100;75},0.75) returns 87.5 for apples, how do you get 168.75?

 

[aefitzgerald]

=PERCENTILE({50;100;75},0.75) returns 87.5 for apples, how do you get 168.75?

225 x 0.75 = 168.75

The table is already sorted such that the rows for each item are in ascending order by price. Price is the variable that I want to find the 75th percentile for... I created the Running Total Qty column in the hope of making that process easier.

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E
1	Item	Quantity	Price	Running Total Qty.
2	Apple	50	$2.00	50
3	Apple	100	$2.25	150
4	Apple	75	$2.75	225	2.75
5	Banana	200	$1.75	200
6	Banana	300	$2.00	500	2
7	Orange	100	$0.50	100
8	Orange	100	$1.00	200
9	Orange	50	$1.25	250
10	Orange	250	$1.75	500	1.75
Sheet7

Cell Formulas
Range	Formula
D2	=SUMIF($A$2:A2,A2,$B$2:B2)
E2	=IF(D2>SUMIF($A$2:$A$10,A2,$B$2:$B$10)*0.75,C2,"")