How to not get '0' or '#DIV/0!' in a cell with a percentage formul

[Rum]

I am trying to get rid of the "zeros" and "#DIV/0!" in a cell with a cell a
percentage formula.

Example:
Column A2 = Numerator
Column B2 = Denominator

Then for % formula I do the following in column A1:
=A2/B2

And this gives me a the cell A1 with percent formula but if the cells A2 and
B2 are blank then it gives "#DIV/0!".

Can we design this formula in such a way that the target cell (A1) remains
empty but still has the formula?

Also, how can I lock the formula cells so that no one can change it?


Please help.

Thanks

 

[NBVC]

If you don't want to show 0 or #Div/0 results.. then

=IF(OR(A2="",B2="",B2=0),"",A2/B2)

or

=IF(SUM(A2,B2)=0,"",A2/B2)

 

[Mike H]

Try this

=IF(COUNT(A2:B2)=2,A2/B2,"")

Mike

 

[Jacob Skaria]

Try

=IF(ISERROR(A2/B2),"",A2/B2)

If this post helps click Yes

 

[Teethless mama]

=IF(N(B2),A2/B2,"")

 

[Rum]

Thanks Jacob.

I have two questions:

1. I am also trying to add contents of different cells like A1, B1, C1,
D1....and my target cell is G1.

If all the cells from A1 to F1 are blank then the result I get in G1 is '0'.
How can I get a blank instead of a '0'.

2. Also, if one of the cells (say B1) has a special character like "*"
(denoting no data available) then the result I get in the target cell G1 is
"#VALUE!". How can I make the target cell (G1) blank while still having the
formula.

Please help.
Rum

 

[Jacob Skaria]

The formula using COUNT() given for the other post should handle * as
well..if you are using SUM()

If this post helps click Yes