subtotal and weighted average

[matt3m]

Hi,

I have Columns A and B.

I want to compute the weighted average of column A. Column B are the weights.

The formula should look something like A*B/(sumB)
I tried

Please Login or Register  to view this content.

I need subtotal because of the ability to filter. It seems to get stuck really just at SUBTOTAL(6,A:A,B:B) because I have a lot of data, and the max number is something like 3 e 300. Help?

Thanks,
Matthew

[NBVC]

Try:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A1:A11,ROW(A1:A11)-MIN(ROW(A1:A11)),,1)),A1:A11,B1:B11)/SUBTOTAL(9,B1:B11)

adjust ranges to suit... it is not recommended to use full column references because of efficiency loss....(and you can't anyway with 2003 version)

[matt3m]

Hi,

Thanks for your reply.

1) My original idea doesn't make any sense bc i thought 6 (for product) did sumproduct, but it doesn't
2) I dont' totally understand NBVC's idea. When I try it, it evaulates to True

Is there a way to get an array of just the filtered numbers?

Also I have excel 2010.

[NBVC]

Attachment 152883See attached for another sample.

This one based on filter being in a different column, like filtering for "b" in column C...

formula would be:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A2:A11,ROW(C2:C11)-MIN(ROW(C2:C11)),,1)),B2:B11)/SUBTOTAL(9,A2:A11)

edit... realized I used count in the denominator instead of Sum (i.e. SUBTOTAL(9....))

[matt3m]

ok it works, but i still can't figure out what is going on in that formula. would you mind explaining?

Thanks so much

[matt3m]

also is there a reason why, in the formulas you have up to 11, even though the numbers in A,B,C only go up to 10?