#Value Being Returned for Blank or Non Zero Value

[Jack]

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?

 

[Pete_UK]

Change your formula in F39 to this:

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

Hope this helps.

Pete

 

[Ragdyer]

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.

 

[Gord Dibben]

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

 

[Ragdyer]

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

RD

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

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

 

[joeu2004]

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.

 

[Jack]

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.

 

[Jack]

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.

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))

 

[Pete_UK]

You're welcome.

Pete

 

[RagDyeR]

Appreciate the feed-back.

Most folks leave the responders hanging.<g>
--

Regards,

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

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.

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))