Formula for percentile if data are in multiple columns

[smide]

Hello.

In columns A and B (A2:A600 and B2:B600) I have product names and in columns C and D their prices. When product is in column A his price is in column C (same row) and when product is in column B his price is in column D.

Note that the same item will not appear in both columns A & B on the same row and likewise prices for some products could be undefined (aka. blank cells) and in that case rows should be ignored for calculation.

I need a formula for 0.3 percentile for each product.

example.

​	A​	B​	C​	D​	E​	F​	G​
1​	​	​	​	product list​	results​
2​	apple​	3​	​	apple​	2.8​
3​	apple​	​	​	orange​	4.9​
4​	orange​
5​	orange​	​	4​
6​	orange​	apple​	6​	1​
7​	apple​	orange​	8​	5​
8​	​	orange​	9​
9​	apple​	​	7​

explanation.

All prices for apple: 3,1,8,7 and 0.3 percentile is 2.8 (cell F2)
All prices for orange: 4,6,5,9 and 0.3 percentile is 4.9 (cell F3)

*note that rows three and four are without price, and they should be disregard for calculation

 

[smide]

I made a mistake with the table, so here is a picture with the correct data.

 

[hagia_sofia]

Hello, would this work:

=AGGREGATE(16,6,$C$2:$D$600/(($A$2:$B$600=$E2)*($C$2:$D$600<>"")),0.3)