Using AutoFilter + sumproduct formula
Where your table is in A1:C4, with headers in A1:C1, try
=SUM(SUBTOTAL(3,OFFSET(B1,ROW(B2:B4)-ROW(B1),1))*B2:B4*C2:C4)/SUBTOTAL(9,B2:B4)
array entered using Cntrl+Shift+Enter
"VLB" wrote:
I have a spreadsheet that has a column that has A and B. It also has a column
that contains a number and another column that contains a %
I have weighted average at the bottom of spreadsheet.
Example:
col A | col B | col C
A-----|---35-|--3.5%
B-----|---49-|--2.7%
B-----|---23-|--1.5%
Average %= x<---------- (sumproduct = (B1:B3,C1:C3)/Sum(B1:B3)
What I need to do is have an autofilter that will filter Column A to show
either All, "A" or "B". When I do this the average formula does not change to
the filtered cells.
I do not know how to use a formula that will select the range for only the
visible (filtered) cells.
In short, how can I do a weighted average for a range of cells using an
autoflter.?
Thanks for any help that you may be able to provide.
|