How keep Excel from counting empty cells when calculating formula

[Guest]

I have plotted out a worksheet, but the problem is that it is trying to
calculate empty cells as having a value of zero. This means that whenever it
is a divide function, it shows up as an error. I need it to count empty
cells as having no value, or to just ignore them, so that it does not affect
related formulas. Any help?

 

[Pete_UK]

Post your formula, then we can comment on it directly.

Pete

 

[David Biddulph]

If your formula is =A1/B1, try =IF(B1="","",A1/B1) or
=IF(OR(A1="",B1=""),"",A1/B1)

 

[Guest]

=PRODUCT(J2,100/G2)

I'm doing a percentage formula. It works when the denominator is present,
and I even tried it without the 100 factor but that didn't work either. I
suppose another option would be if I would get Excel to tell me that J2 is x
percent of G2. Don't know how to do that either.

 

[Pete_UK]

If you try to divide by G2 and G2=0 then you will get the #DIV/0
error, so this is the condition you need to trap - you can do it like
this:

=IF(OR(G2=0,G2=""),"",PRODUCT(J2,100/G2))

This will show a blank cell if G2 is zero, or blank. If you would
prefer your cell to show zero, then change the second "" to 0.

Hope this helps.

Pete

 

[Guest]

That worked. Thanks to Pete UK and David Biddulph

 

[Pete_UK]

You're welcome.

Pete

That worked. Thanks to Pete UK and David Biddulph






- Show quoted text -