Complicated Median help

[rollwrig]

I am having problems putting this into words, so bear with me.
I am attempting to find the Median which I normally know how to do. The problem is something else. I have 4 columns

Sales Person
Product
# of Products Sold
Price

I want to find the Median price of products sold for each Sales person. The problem comes when they have sold more then one of a product. My current list is 3600+ rows long otherwise I would manually add all the products in. I have tried pasting in an example below. The only way I can think to do it is to manually add in a cell for each and every product sold. Ex. Row 1, I would add in $27 again. Same for Row 2. Row 4 would have me add 5 additional cells containing $38.00. Can anyone help?


[TABLE="width: 352"]
<tbody>[TR]
[TD="align: center"]Sales Person[/TD]
[TD="align: center"]Product Code[/TD]
[TD="align: center"]No Of Bottles[/TD]
[TD="align: center"]Price[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]A166[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$27.00[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]A166[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$27.00[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]A4990[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$38.00[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]A4990[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]$38.00[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]B1192[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]$21.00[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]B3053[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$45.00[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]B3053[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]$45.00[/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]B3053[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]$45.00[/TD]
[/TR]
</tbody>[/TABLE]



Rollie Wright

 

[GerryZ]

TRY:

Book1
	A	B	C	D	F	G
1	Sales Person	Product Code	No Of Bottles	Price	criteria1	MEDIA
2	Bob	A166	2	€ 27,00	Bob	110,375
3	Bob	A166	2	€ 27,00
4	Bob	A4990	2	€ 38,00
5	Bob	A4990	6	€ 38,00
6	Bob	B1192	1	€ 21,00
7	Bob	B3053	2	€ 45,00
8	Bob	B3053	2	€ 45,00
9	Bob	B3053	6	€ 45,00
Foglio2

Cell Formulas
Range	Formula
G2	{=AVERAGE(IF(A2:A9=F2,D2:D9*C2:C9))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[barry houdini]

Hello Rollie, welcome to MrExcel,

If you want the median price for all products sold by Bob try this "array formula"

=MEDIAN(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(C2:C10))))<=C2:C10,IF(A2:A10="Bob",D2:D10)))

confirmed with CTRL+SHIFT+ENTER

 

[rollwrig]

GerryZ. This one seems to calculate a number that can't be right. 54?

 

[rollwrig]

barry houdini You rock. Thank you for the help.