I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show up
as values RESULTING from formulas?

Change the formulas !

OR

If you don't need the formulas for any *further* calculations,
Select the formulas,
right click in the selection,
Choose "Copy",
Right click again and choose "Paste Special",
Click on "Values", then <OK.

Now you can edit and replace the 0's with blanks,

Which is *EXACTLY* the same as *DELETING* the formulas in the first
place.<g

OR

Change the formulas !
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Riverrat" wrote in message
...
I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show up
as values RESULTING from formulas?

If you simply don't want to see anything where the cell value is zero, you
could use Tools / Options, select the View tab and clear the 'zero values'
checkbox.

"Riverrat" wrote:

I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show up
as values RESULTING from formulas?

You can't by using find and replace, you can however do it in the same
formula that might return a zero

=IF(yourformula=0,"",yourformula)

note that if you do calculation using an operand the result will be a value
error since the blank is text,
if you are plotting a graph then you should use the NA() function instead

=IF(yourformula=0,NA(),yourformula)

you might then want to hide the result since it will look like #N/A! so you
can use conditional formatting, formula is

=ISNA(A2)

(where A2 is the cell that holds the original formula), format with white
fonts

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Riverrat" wrote in message
...
I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to
formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show
up
as values RESULTING from formulas?

I'll need the formulas possibly later so I'd rather not change them.
Otherwise I'd do just as you suggested.

"RagDyeR" wrote:

Change the formulas !

OR

If you don't need the formulas for any *further* calculations,
Select the formulas,
right click in the selection,
Choose "Copy",
Right click again and choose "Paste Special",
Click on "Values", then <OK.

Now you can edit and replace the 0's with blanks,

Which is *EXACTLY* the same as *DELETING* the formulas in the first
place.<g

OR

Change the formulas !
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Riverrat" wrote in message
...
I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show up
as values RESULTING from formulas?

Thank you for the suggestion--it opened my eyes to a view option I wasn't
previously aware of--but I'll be taking averages of some of these rows
containing zeros. If the zeros are present but I'm not aware of them because
I turned them off, my averages will be skewed low.

"bpeltzer" wrote:

If you simply don't want to see anything where the cell value is zero, you
could use Tools / Options, select the View tab and clear the 'zero values'
checkbox.

"Riverrat" wrote:

I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show up
as values RESULTING from formulas?

Do I add this "IF" formula to the original formula or can I somehow overlay
it on the cells containing the original formula?
It seems your suggestion is the best so far for my needs.

"Peo Sjoblom" wrote:

You can't by using find and replace, you can however do it in the same
formula that might return a zero

=IF(yourformula=0,"",yourformula)

note that if you do calculation using an operand the result will be a value
error since the blank is text,
if you are plotting a graph then you should use the NA() function instead

=IF(yourformula=0,NA(),yourformula)

you might then want to hide the result since it will look like #N/A! so you
can use conditional formatting, formula is

=ISNA(A2)

(where A2 is the cell that holds the original formula), format with white
fonts

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Riverrat" wrote in message
...
I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to
formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show
up
as values RESULTING from formulas?

Peo is saying the same thing I said, but using different words:

*Change your formulas !*

Post back with a formula you're using, and we'll gladly show you how to
revise it to eliminate the 0's.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Riverrat" wrote in message
...
Do I add this "IF" formula to the original formula or can I somehow overlay
it on the cells containing the original formula?
It seems your suggestion is the best so far for my needs.

"Peo Sjoblom" wrote:

You can't by using find and replace, you can however do it in the same
formula that might return a zero

=IF(yourformula=0,"",yourformula)

note that if you do calculation using an operand the result will be a
value
error since the blank is text,
if you are plotting a graph then you should use the NA() function instead

=IF(yourformula=0,NA(),yourformula)

you might then want to hide the result since it will look like #N/A! so
you
can use conditional formatting, formula is

=ISNA(A2)

(where A2 is the cell that holds the original formula), format with white
fonts

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Riverrat" wrote in message
...
I want to replace zeros with blanks. In the Find tab of the Find/Replace
dialog box I can choose to find the zeros in values as opposed to
formulas.
However, when I go to the Replace tab, the only option it gives me is to
replace zeros found in formulas. How can I replace only zeros that show
up
as values RESULTING from formulas?

Exactly as Peo said

=IF(yourformula=0,"",yourformula)

Take your formula, whatever it is, EXCEPT the = sign, and put it in the
formula above in both places where you can see it say yourformula, eg in
cell A2 lets assume you have the following

=SUM(B2:Z2)

Simply make it

=IF(SUM(B2:Z2)=0,"",SUM(B2:Z2))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Riverrat" wrote in message
...
Do I add this "IF" formula to the original formula or can I somehow
overlay
it on the cells containing the original formula?
It seems your suggestion is the best so far for my needs.

"Peo Sjoblom" wrote:

You can't by using find and replace, you can however do it in the same
formula that might return a zero

=IF(yourformula=0,"",yourformula)

note that if you do calculation using an operand the result will be a
value
error since the blank is text,
if you are plotting a graph then you should use the NA() function instead

=IF(yourformula=0,NA(),yourformula)

you might then want to hide the result since it will look like #N/A! so
you
can use conditional formatting, formula is

=ISNA(A2)

(where A2 is the cell that holds the original formula), format with white
fonts

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Riverrat" wrote in message
...
I want to replace zeros with blanks. In the Find tab of the
Find/Replace
dialog box I can choose to find the zeros in values as opposed to
formulas.
However, when I go to the Replace tab, the only option it gives me is
to
replace zeros found in formulas. How can I replace only zeros that
show
up
as values RESULTING from formulas?