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

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

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

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)


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111335

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

Try this

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

Mike

"Rum" wrote:

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

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

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Rum" wrote:

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

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

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


"Rum" wrote:

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

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

Hi,

Try

=IF(B1=0,"",A1/B1)

or the shorter

=IF(B1,A1/B1,"")

And regarding the second question: You need to protect the worksheet -
Select all the cells you want to be unlocked and choose Format, Cells,
Protection, and uncheck Locked. Next choose Tools, Protection, Protect
Sheet...

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rum" wrote:

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

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

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" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Rum" wrote:

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

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

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

If this post helps click Yes
---------------
Jacob Skaria


"Rum" wrote:

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" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Rum" wrote:

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