#Value Being Returned for Blank or Non Zero Value

J

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?
 
P

Pete_UK

Change your formula in F39 to this:

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

Hope this helps.

Pete
 
R

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.
 
G

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
 
R

Ragdyer

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Gord Dibben said:
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
 
J

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.
 
J

Jack

Pete_UK said:
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.
 
J

Jack

Ragdyer said:
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))
 
R

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


Ragdyer said:
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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top