multiple averages

[andy callaghan]

I need a formula that will calculate the average price for a particular part number.

There are 500 different parts number in column A, each price is different and is in column b

can I do a sumproduct average combo???!!. this one has me totally baffled

many thnaks
andy

[Domenic]

Try the following...

=SUMIF(A1:A100,PartNumber,B1:B100)/COUNTIF(A1:A100,PartNumber)

or

=AVERAGE(IF(A1:A100=PartNumber,B1:B100))

...confirmed with CONTROL+****+ENTER.

If you'd like to avoid an error value when no data or part number exists...

=SUMIF(A1:A100,PartNumber,B1:B100)/MAX(1,COUNTIF(A1:A100,PartNumber))

=IF(COUNTIF(A1:A100,PartNumber),AVERAGE(IF(A1:A100=PartNumber,B1:B100)),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

[andy callaghan]

I used

=SUMPRODUCT(--('Margin Report'!$A$2:$A$6000=$A9),'Margin Report'!$B$2:$B$6000)/SUMPRODUCT(--('Margin Report'!$A$2:$A$6000=$A9))

this worked to figure out the average so on ect but I couldnt get it to return a blank if no part number exsisted. I tried ,"") at the end windows wouoldnt let me apply.

can you help

Andy

[Domenic]

Since you have only one condition, SUMIF would suffice. But if you insist...

=SUMPRODUCT(--('Margin Report'!$A$2:$A$6000=$A9),'Margin Report'!$B$2:$B$6000)/MAX(1,SUMPRODUCT(--('Margin Report'!$A$2:$A$6000=$A9)))

or

=IF(COUNTIF($A$2:$A$6000,$A9),SUMPRODUCT(--('Margin Report'!$A$2:$A$6000=$A9),'Margin Report'!$B$2:$B$6000)/SUMPRODUCT(--('Margin Report'!$A$2:$A$6000=$A9)),"")

Hope this helps!