Show weighted average value after filter.

[Guest]

Problem.
Using the SUBTOTAL(1,D513) will return the simple average after I have
filtered a list of values in colum d or another colum. However I want to show
the Weighted average correctly after a filter is applied.
I.E Currently I use this, =SUMPRODUCT(D512,$J$5:$J$12)/SUM($J$5:$J$12) but
the value will not change if I filter and of the colums.

Hope someone can help and thatnks in advance.

 

[Peo Sjoblom]

One way


=SUMPRODUCT(--($D$5:$D$12),--($J$5:$J$12),(SUBTOTAL(3,OFFSET($J$5,ROW($J$5:$J$12)-MIN(ROW($J$5:$J$12)),,))))/SUBTOTAL(9,$J$5:$J$12)

 

[Aladin Akyurek]

One way


=SUMPRODUCT(--($D$5:$D$12),--($J$5:$J$12),(SUBTOTAL(3,OFFSET($J$5,ROW($J$5:$J$12)-MIN(ROW($J$5:$J$12)),,))))/SUBTOTAL(9,$J$5:$J$12)

A bit shorter, also expecting no text-formatted numbers:

=SUMPRODUCT($D$5:$D$12,SUBTOTAL(9,OFFSET($J$5,ROW($J$5:$J$12)-MIN(ROW($J$5:$J$12)),,)))/SUBTOTAL(9,$J$5:$J$12)

On Excel 2003, I'd turn the data area into a list by running
Data|List|Create List. The setup allows AutoFiltering and the ranges in
the Subtotal formula are automatically updated when new records are
added or records are deleted.

 

[Guest]

Assuming you're filtering column D with some simple criteria, this works just
fine. Change the ">5" to your filter criteria.

=SUMPRODUCT(--(D512>5),D512,E5:E12)/SUMPRODUCT(--(D512>5),D512)