How do I get rid of #DIV/0! part 2

[dktani89]

Hi

I tried the suggestions that were given to me. The only problem is
that they will show a percentage in .00 instead of %. They want me to
get the the answer in %. I just cannot get this to work. It will
work once the cells are filled and it computes. But until then the
cell shows #DIV/0!


Original Message:

Hi,

How do I get rid of #DIV/0! in my cell when I used the following
formula?


=IF(Q3="","",1-(Q3/F3))


I have tried everything and it still shows up in my cells that use
this formula.


thanks for anyhelp that you can give me.

 

[Guest]

First format the cell as %

then:

=IF(F3="","",1-(Q3/F3))

 

[joeu2004]

I tried the suggestions that were given to me. The only
problem is that they will show a percentage in .00 instead
of %. They want me to get the the answer in %.

One of the problems with posting a follow-up in a new thread is that
people who want to help lose the context of the original thread. I
cannot be sure what is the original problem and what is you failed
attempt to correct the original problem.

Original Message:
How do I get rid of #DIV/0! in my cell when I used the following
formula?
=IF(Q3="","",1-(Q3/F3))

If you want a blank result when Q3 is blank, but you want 0% when F3
is blank or zero (the cause of the #DIV/0 error), try:

=if(Q3="", "", if(F3=0, 0, 1 - Q3/F3))

Format the cell to display percentage by clicking on Format -> Cells -

Number -> Percentage and select the desired number of decimal

places.

That will display 100% when Q3 is zero, not blank. I ass-u-me that is
what you want.

However, if you want 100% when Q3 is blank or zero and Q3="" is just a
typo in your posting, try:

=if(F3=0, 0, 1 - Q3/F3)

No need to test Q3. It "falls out" of the arithmetic.

HTH.

 

[joeu2004]

Errata....

Original Message:
[....]
=IF(Q3="","",1-(Q3/F3))

[....]
=if(Q3="", "", if(F3=0, 0, 1 - Q3/F3))
[....]
=if(F3=0, 0, 1 - Q3/F3)

Klunk! I keep forgetting that Excel does not treat a cell with the
null string ("") the same as a blank cell, even though they look the
same.

Also, there is a case or two that might be your original intent, which
I overlooked. In a nutshell....

1. If you want a blank result only when both Q3 and F3 are blank, and
a numeric result otherwise (avoiding #DIV/0):

=if(and(Q3="",F3=""), "", if(n(F3)=0, 0, 1 - Q3/F3))

2. If you want a blank result when either Q3 or F3 is blank, and a
numeric result otherwise (avoiding #DIV/0):

=if(or(Q3="",F3=""), "", if(n(F3)=0, 0, 1 - Q3/F3))

3. If you want a blank result only when Q3 is blank (as indicated by
your posting), and a numeric result otherwise (avoiding #DIV/0):

=if(Q3="", "", if(n(F3)=0, 0, 1 - Q3/F3))

4. If you want a numeric result in all cases (avoiding #DIV/0), even
when Q3 or F3 is blank:

=if(n(F3)=0, 0, 1 - Q3/F3)

The N() function converts the null string ("") to zero. It does not
seem to be needed when the null string is in an arithmetic formula
(e.g. Q3/F3). But F3=0 works only when F3 is truly empty or zero, not
when it contains the null string (e.g. the result of IF(...,
"", ...)).

HTH.