Formula to include quantities in percentile calculation

[richard_d1]

We manufacture products and then cut to them to the length customers ask for. I have a sheet containing all our orders, and I'm trying to write a formula to calculate what length 90% of our orders for each product are over. So if we have any offcuts, we know if it's worth saving them or not.

I've done this so far:

Book2
	A	B	C	D	E	F	G
1	Order No	Product	Length	Quantity		Product	90%
2	1001	B	3.050	5		A	3.100
3	1002	B	2.125	28		B	3.606
4	1003	A	3.100	3		C	5.155
5	1004	A	3.100	1
6	1005	B	2.400	4
7	1006	B	2.400	6
8	1007	B	2.400	8
9	1008	B	2.200	11
10	1009	B	3.050	10
11	1010	C	4.750	151
12	1011	C	5.200	19
13	1012	A	1.400	4
14	1013	B	3.050	20
15	1014	B	3.600	10
16	1015	B	3.660	2
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=PERCENTILE(IF(B:B=F2,C:C),1-$G$1)

So in column G, it calculates what length 90% of our orders were over.

However, that doesn't take into account the size of the order. How can I change the formula to include the quantity?

Thanks.

 

[kvsrinivasamurthy]

I am not clear what is required. May this help.

=PERCENTILE(IF(B:B=F2,C:C*D:D),1-$G$1)

It is a better practice to avoid referring full row or column and use specified range.
Eg; Instead of C:C use say C2:C200

 

[richard_d1]

I am not clear what is required. May this help.

=PERCENTILE(IF(B:B=F2,C:C*D:D),1-$G$1)

It is a better practice to avoid referring full row or column and use specified range.
Eg; Instead of C:C use say C2:C200

Thanks. That doesn't do what I'm after though I'm afraid.

To explain it better, what I want it to do for Product A:

Book1
	J	K	L	M	N	O
1	Order No	Product	Length		Product	90%
2	1003	A	3.1		A	1.4
3	1003	A	3.1
4	1003	A	3.1
5	1004	A	3.1
6	1012	A	1.4
7	1012	A	1.4
8	1012	A	1.4
9	1012	A	1.4
Sheet1

Cell Formulas
Range	Formula
O2	O2	=PERCENTILE(L2:L9,1-$O$1)

So where the quantity is 3, it repeats that line 3 times. And then takes calculates the percentile.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Order No	Product	Length	Quantity		Product	90%
2	1001	B	3.05	5		A	1.4
3	1002	B	2.125	28		B	2.125
4	1003	A	3.1	3		C	4.75
5	1004	A	3.1	1
6	1005	B	2.4	4
7	1006	B	2.4	6
8	1007	B	2.4	8
9	1008	B	2.2	11
10	1009	B	3.05	10
11	1010	C	4.75	151
12	1011	C	5.2	19
13	1012	A	1.4	4
14	1013	B	3.05	20
15	1014	B	3.6	10
16	1015	B	3.66	2
Sheet6

Cell Formulas
Range	Formula
G2:G4	G2	=LET(f,FILTER($C$2:$D$100,$B$2:$B$100=F2),q,INDEX(f,,2),x,TOCOL(IF(SEQUENCE(,MAX(q))<=q,INDEX(f,,1),1/0),2),PERCENTILE(x,1-$G$1))

 

[richard_d1]

@Fluff Thanks; that did it! No idea how it works, but it gets the result I'm after.

90% of the Product A's we supplied were over 1.4m.

 

[Fluff]

Glad we could help & thanks for the feedback.