# 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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

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

### Similar threads

Replies
3
Views
689
Replies
4
Views
204
Replies
3
Views
218
Replies
2
Views
105
Replies
5
Views
46

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Threads
1,151,589
Messages
5,765,317
Members
425,272
Latest member
Umba

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

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