Prevent #DIV/0! when using SUMPRODUCT

[westend9876]

I have two named ranges:

Name of range is CS_total
Completed-Sales!$G$3:$G$500

Name of range AS_total
Abandoned-Sales!$G$3:$G$500

Here is my formula in Results!G3
SUMPRODUCT(CS_total/AS_total)/150

The problem is than some cells in $G$3:$G$500 contain 0 or are blank.
How do I create a formula that disregards 0 and blank cells?

Also, the 150 is the number of rows with actual values greater than 0.

 

[WMF]

=SUM(IFERROR(A1:A6/B1:B6,0)/150)
Ctrl Shift Enter

replace the range accordingly

 

[westend9876]

=SUM(IFERROR(A1:A6/B1:B6,0)/150)
Ctrl Shift Enter

replace the range accordingly

Thank you WMF. Here is the next dependent formula based on the results:

SUMPRODUCT(((CS_total/AS_total)-$G$3)^2)

I get the same #DIV/0! problem. Should I use IFERROR here too? If so how or what would be your solution? Thanks.

 

[oldchippy]

Hi westend9876,

If it worked the first time, give it a try

=SUM(IFERROR(((A1:A6/B1:B6)-$G$3)^2,0)
Ctrl+Shift+Enter