Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula and multiplying conditions | Excel Worksheet Functions | |||
Is it possible? | Excel Worksheet Functions | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |