Calculating a weighted average in visible cells with an IF variable

Joined
Aug 9, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I am trying to get Excel to calculate the following:

Weighted average for visible cells for each variable within Column K
Column K: Job #
Column L: Pay Rate
Column M: Frequency
Column N: Pay Rate * Freq (Helper column)(If necessary)

The following formula works for all cells within the worksheet. I need it to be applied to only visible cells after I use the filter

=SUMPRODUCT($M$14:$M$511*$L$14:$L$511*($K$14:$K$511=A1))/SUMIF($K$14:$K$511,A1,$M$14:$M$511)

I am referencing the column K variables by putting them into A1-8 (you see it referenced in A1)

Thanks for your help. It sounds like I have to use some sort of SUBTOTAL function but for the life of me, I can't get it to work.

Also feel free to correct my absolute references above. I'm not entirely sure I need them here.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,686
Welcome to the MrExcel forum!

Try:

Book1
ABCDEFGHIJKLM
1A3.0666673.375
2B2.6666672
3C3.4285712
4D3.25#DIV/0!
5E#DIV/0!#DIV/0!
6F#DIV/0!#DIV/0!
7G#DIV/0!#DIV/0!
8H#DIV/0!#DIV/0!
9
10
11
12
13Job #Pay RateFrequency
14A21
24C22
25A33
26A44
27B21
28
Sheet25
Cell Formulas
RangeFormula
B1:B8B1=SUMPRODUCT($M$14:$M$511*$L$14:$L$511*($K$14:$K$511=A1))/SUMIF($K$14:$K$511,A1,$M$14:$M$511)
C1:C8C1=SUMPRODUCT($M$14:$M$511*$L$14:$L$511*($K$14:$K$511=A1)*SUBTOTAL(103,OFFSET($K$14,ROW($K$14:$K$511)-ROW($K$14),0)))/SUMPRODUCT($M$14:$M$511*($K$14:$K$511=A1)*SUBTOTAL(103,OFFSET($K$14,ROW($K$14:$K$511)-ROW($K$14),0)))


Your formula is in B1, the filtered one is in C1.
 
Joined
Aug 9, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Thank you so much! I really appreciate it. This is going to save a lot of people a lot of work and make our product much more accurate :)
 

Forum statistics

Threads
1,148,340
Messages
5,746,186
Members
423,998
Latest member
eakenila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top