Using AutoFilter + sumproduct formula
 

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.

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.

Using AutoFilter + sumproduct formula
 

Cheers!

"JMB" wrote:

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.