Excel 2007 : Weighted Average Price - FILTERED

I am using this function to feed back the weighted average prices:

=SUMPRODUCT(Price per unit Range,Number of units Range)/SUM(Number of units Range)

However, I have filters at the top of my sheet to turn visibility on and off various products. If I used a SUBTOTAL function it only calculates the visible cells - GREAT!

Is there a way I can use my weighted average formula ONLY on visible cells when using filters?

Current formula in use is:

=SUMPRODUCT(M2:M91,H2:H91)/SUM(H2:H91)

Solution not working:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($M$2:$M$91,ROW($M$2:$M$91)-MIN(ROW($M$2:$M$91)),0,1)),$M$2:$M$91,$H$2:$H$91)/SUBTOTAL(9,$H$2:$H$91)

When filtered shows same result every time. What is wrong?

You need only account for the M range once - ie via the SUBTOTAL SUM call.

Using your sample formula:

=SUMPRODUCT(SUBTOTAL(9,OFFSET($M$2:$M$91,ROW($M$2:$M$91)-MIN(ROW($M$2:$M$91)),0,1)),$H$2:$H$91)/SUBTOTAL(9,$H$2:$H$91)

Using the version within the file (different range references)

=SUMPRODUCT(SUBTOTAL(9,OFFSET($M$3:$M$300,ROW($M$3:$M$300)-MIN(ROW($M$3:$M$300)),0,1)),$F$3:$F$300)/SUBTOTAL(9,$F$3:$F$300)

I can't determine from your post if you're implying there is an issue or not ?

The file (& formula) is correct.

You can verify the calculations by adding some basic formulas:

With no filter applied (ie all rows visible)

Please Login or Register  to view this content.

Your weighted average for visible rows can then also be calculated using a very basic:

Please Login or Register  to view this content.