# Calculating a weighted average in visible cells with an IF variable

#### excelisfunforeveryone

##### New Member
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

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
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.

#### excelisfunforeveryone

##### New Member
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

Replies
3
Views
587
Replies
4
Views
84
Replies
3
Views
189
Replies
7
Views
157
Replies
0
Views
226

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.

### Which adblocker are you using?

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

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