Assuming you're filtering column D with some simple criteria, this works just
fine. Change the "5" to your filter criteria.
=SUMPRODUCT(--(D5:D125),D5:D12,E5:E12)/SUMPRODUCT(--(D5:D125),D5:D12)
"BillC" wrote:
Problem.
Using the SUBTOTAL(1,D5:D13) 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(D5:D12,$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.
|