Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
BillC
 
Posts: n/a
Default 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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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   Report Post  
Duke Carey
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
weighted average in pivot table nasser Excel Discussion (Misc queries) 3 January 18th 06 05:36 PM
Why does my advance filter only show one record? Kyri Excel Discussion (Misc queries) 1 March 25th 05 04:13 AM
Weighted Average Aloysicus Excel Discussion (Misc queries) 4 January 5th 05 12:10 PM
Excel Auto Filter: WHY'S SORT @ TOP OF LIST? WHEN I KEY TO "SHOW . Dan W Excel Worksheet Functions 0 December 1st 04 04:53 PM
What is the formula for weighted average? Seth23hare Excel Worksheet Functions 1 November 23rd 04 09:49 PM


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"