Getting the #DIV/0! Error but I want to divide by 0

Hello,

I have a feeling this is really simple but I cannot get it to work and I am running out of time to get people paid bonuses!

I have attached the spreadsheet I am working with.

In Rows 2-6 I have formulas entered to compute if someone hit their bonus goals. It returns a Y or a N. In some cases though I have it returning blank because that didn't apply to that property. Then in Column G I want it to take the total Y responses divided by the total cells that returned a response and times that by the total bonus % possible. So in my attached example (Tab EOM-HOM-611-GB) Row 5 I get the desired result. But when I have all Y or all N I am getting the error. G4 Should return 0 and and G3 should return 10.

Any help is greatly appreciated!

Hi heresteve2,
You said, " divided by the total cells that returned a response" but your formulas are dividing by the number (Countblank) that do not return a response. In Row 2 there are zero cells that did not return a response and since you cannot mathematically divide by zero you get the error.

Try this formula in G2:

=(COUNTIF(B2:E2, "Y")/COUNTA(B2:E2))*F2

then copy down to G6. Change the formula in G7 to this:

=SUM(G2:G6)/100

Hope this helps.

Pete

Hi Glenn,

This seemed to do the trick. Thank you so much!