calc average of preset sum

[adilift]

how can I calculate a conditional average for a preset sum. I have a the following table:
stock qty type price
msft 2 b 50
msft 2 b 50
dell 2 b 40
msft 2 s 100
msft 2 b 45

I am trying to find the average price of a preset 'qty' where stock is 'msft' and type is 'b'

I am using the following
=AVERAGEIFS(D2:D6,A2:A6,"msft",C2:C6,"b")
which returns 48.33

but i would like to calc average of a preset qty sum (cell F1).e.g avg for total qty '5' along with above conditions should be:
(2*50 + 2*50 + 1*45)/5= 49

VBA function would be real helpful

 

[jim may]

why (2*50 + 2*50 + 1*45)/5
when i see in your table..
(2*50 + 2*50 + 2*45)/5

 

[adilift]

I am interested in finding the avg of total qty 5 , not 6
so basically
(2*50 + 2*50 + 1*45)/5

instead of

(2*50 + 2*50 + 2*45)/6

 

[Dave Patton]

=SUMPRODUCT(--(A2:A6=A9),--(C2:C6=C9),(B2:B6*D2:D6))/SUMPRODUCT(--(A2:A6=A9),--(C2:C6=C9),(B2:B6))

Will give your first number.

If you put your desired quantity in an adjacent column such as E, the following formula will give the required number.

=SUMPRODUCT(--(A2:A6=A9),--(C2:C6=C9),(E2:E6*D2:D6))/SUMPRODUCT(--(A2:A6=A9),--(C2:C6=C9),(E2:E6))