Adjust weighted average (sumproduct) for empty cells

**robertgroen92** · 03-19-2014, 06:46 AM

I'm calculating a weighted average using the sumproduct function as follows: =SUMPRODUCT(L13:L20, $V$25:$V$32)/SUM($V$25:$V$32). Where the values are in the L column and the weights are in the V column.

The problem is that sometimes there's an empty cell in the L column, which really scews the weighted average. I'd like the function to either calculate the weighted average for the cells that do have a value, or it would also be fine if the outcome of the function just shows a problem because on or more of the cells in column L are empty.

I can't seem to fix it, any help is appreciated.

**JosephP** · 03-19-2014, 07:13 AM

you can try
=SUMPRODUCT((L13:L20<>"")*L13:L20, $V$25:$V$32)/SUMIF(L13:L20,"<>",$V$25:$V$32)

**robertgroen92** · 03-19-2014, 08:08 AM

This couldn't work any better. Thanks a lot for your help man, really appreciate it!

**daddylonglegs** · 03-19-2014, 08:13 AM

SUMPRODUCT will ignore blanks anyway so really you only need to replace the SUM with SUMIF, i.e.

=SUMPRODUCT(L13:L20, $V$25:$V$32)/SUMIF(L13:L20,"<>",$V$25:$V$32)