#Value Being Returned for Blank or Non Zero Value

I am referring to a cell in my spreadsheet that currently has no value
associated to it. I have a formula in the cell that refers back to another
cell. For example in cell F40 there is a calculation of =H40. In cell H40 I
have =G40*F39.

G40 and F39 are both blank. When I enter data into the spreadsheet these
cells will then be populated/calculate from the previously entered data.

Cell F39 is returning #VALUE. Is there a way for me to have this field
return a blank, if the value it's referring to is zero or blank?

Change your formula in F39 to this:

=IF(ISERROR(your_formula),0,your_formula)

Hope this helps.

Pete

On Nov 11, 8:05 pm, "Jack" wrote:
I am referring to a cell in my spreadsheet that currently has no value
associated to it. I have a formula in the cell that refers back to another
cell. For example in cell F40 there is a calculation of =H40. In cell H40 I
have =G40*F39.

G40 and F39 are both blank. When I enter data into the spreadsheet these
cells will then be populated/calculate from the previously entered data.

Cell F39 is returning #VALUE. Is there a way for me to have this field
return a blank, if the value it's referring to is zero or blank?

What's in G40 and F39?

You say they're *both blank*, but F39 is returning a #Value! error!
How can it be *both ways*?
Do they contain formulas?
A #Value! error can come from a formula performing calcs on a cell
containing text.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jack" wrote in message
...
I am referring to a cell in my spreadsheet that currently has no value
associated to it. I have a formula in the cell that refers back to another
cell. For example in cell F40 there is a calculation of =H40. In cell H40
I
have =G40*F39.

G40 and F39 are both blank. When I enter data into the spreadsheet these
cells will then be populated/calculate from the previously entered data.

Cell F39 is returning #VALUE. Is there a way for me to have this field
return a blank, if the value it's referring to is zero or blank?

My take is the reference to F39 returning #VALUE! is that the F39 is a typo and
should read F40 returns #VALUE!

Possibly a space in blank cells F39 or G40 so the formula in H40 should return
#VALUE! also.


Gord Dibben MS Excel MVP

On Sun, 11 Nov 2007 13:24:41 -0800, "Ragdyer" wrote:

What's in G40 and F39?

You say they're *both blank*, but F39 is returning a #Value! error!
How can it be *both ways*?
Do they contain formulas?
A #Value! error can come from a formula performing calcs on a cell
containing text.

Let's see if Jack gets back.g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gord Dibben" gorddibbATshawDOTca wrote in message
...
My take is the reference to F39 returning #VALUE! is that the F39 is a
typo and
should read F40 returns #VALUE!

Possibly a space in blank cells F39 or G40 so the formula in H40 should
return
#VALUE! also.


Gord Dibben MS Excel MVP

On Sun, 11 Nov 2007 13:24:41 -0800, "Ragdyer"
wrote:

What's in G40 and F39?

You say they're *both blank*, but F39 is returning a #Value! error!
How can it be *both ways*?
Do they contain formulas?
A #Value! error can come from a formula performing calcs on a cell
containing text.

On Nov 11, 12:05 pm, "Jack" wrote:
For example in cell F40 there is a calculation of =H40. In cell H40 I
have =G40*F39.

G40 and F39 are both blank. When I enter data into the spreadsheet these
cells will then be populated/calculate from the previously entered data.

Cell F39 is returning #VALUE. Is there a way for me to have this field
return a blank, if the value it's referring to is zero or blank?

First, you say F39 is blank; then you say F39 is #VALUE. Which is it?

Regardless, to answer your last question, perhaps the N() function
satisfies your needs. You might use it one of two ways. For example:

=n(G40)*n(F39)

Or:

=if(n(H39)=0, "", H39)

On problem in Excel is: an empty cell is treated like zero, but the
null string ("", which looks like an empty cell) is not. I wonder if
that is the source of your #VALUE error. If so, the examples above
will mitigate that.

"Pete_UK" wrote in message
ups.com...
Change your formula in F39 to this:

=IF(ISERROR(your_formula),0,your_formula)

Hope this helps.

Pete


Pete, I modified your formula slightly
=IF(ISERROR(F39*G40),"",(F39*G40))
and got the effect that I wanted. Thanks for your help.

"Ragdyer" wrote in message
...
What's in G40 and F39?

You say they're *both blank*, but F39 is returning a #Value! error!
How can it be *both ways*?
Do they contain formulas?
A #Value! error can come from a formula performing calcs on a cell
containing text.

--
Regards,

RD

G40 is my the tax rate of 7.375% and F39 is
=IF(SUM(F18:F38)0,SUM(F18:F38),"")

What I am doing is multiply my tax rate times the subtotal. If the
spreadsheet is blank without any values, then it WAS returning a #value!
error message. I have since cleaned up the formula in cell F40 with this
formula:
=IF(ISERROR(F39*G40),"",(F39*G40))

You're welcome.

Pete

On Nov 12, 6:23 am, "Jack" wrote:

Pete, I modified your formula slightly
=IF(ISERROR(F39*G40),"",(F39*G40))
and got the effect that I wanted. Thanks for your help.

Appreciate the feed-back.

Most folks leave the responders hanging.g
--

Regards,

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

"Jack" wrote in message
...

"Ragdyer" wrote in message
...
What's in G40 and F39?

You say they're *both blank*, but F39 is returning a #Value! error!
How can it be *both ways*?
Do they contain formulas?
A #Value! error can come from a formula performing calcs on a cell
containing text.

--
Regards,

RD

G40 is my the tax rate of 7.375% and F39 is
=IF(SUM(F18:F38)0,SUM(F18:F38),"")

What I am doing is multiply my tax rate times the subtotal. If the
spreadsheet is blank without any values, then it WAS returning a #value!
error message. I have since cleaned up the formula in cell F40 with this
formula:
=IF(ISERROR(F39*G40),"",(F39*G40))