Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Show weighted average value after filter.
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. |
#2
|
|||
|
|||
One way
=SUMPRODUCT(--($D$5:$D$12),--($J$5:$J$12),(SUBTOTAL(3,OFFSET($J$5,ROW($J$5:$J$1 2)-MIN(ROW($J$5:$J$12)),,))))/SUBTOTAL(9,$J$5:$J$12) -- Regards, Peo Sjoblom "BillC" wrote in message ... 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. |
#3
|
|||
|
|||
Peo Sjoblom wrote:
One way =SUMPRODUCT(--($D$5:$D$12),--($J$5:$J$12),(SUBTOTAL(3,OFFSET($J$5,ROW($J$5:$J$1 2)-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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weighted average in pivot table | Excel Discussion (Misc queries) | |||
Why does my advance filter only show one record? | Excel Discussion (Misc queries) | |||
Weighted Average | Excel Discussion (Misc queries) | |||
Excel Auto Filter: WHY'S SORT @ TOP OF LIST? WHEN I KEY TO "SHOW . | Excel Worksheet Functions | |||
What is the formula for weighted average? | Excel Worksheet Functions |