#value!

D

Desert Piranha

Hi,

In G4 i have the results of a formula which can be less than one.
In H4 there are whole numbers or a 0.

If the sum of G4-H4 is less than 1 then nothing.
If the sum of G4-H4 is 1 or more then the sum of G4-H4.

'=IF(SUM(G4-H4<=1),"",SUM(G4-H4))

If G4 has less than 1 or a fraction of 1 then i get the dreaded #VALUE!
thingy.

This seems to work UNLESS one of the cells feeding the formula in G4 is
empty
then i also the #VALUE! thingy.
=IF(H4="","",IF(H4<G4,G4-H4,0))

Any direction?
 
R

Ron Rosenfeld

Hi,

In G4 i have the results of a formula which can be less than one.
In H4 there are whole numbers or a 0.

If the sum of G4-H4 is less than 1 then nothing.
If the sum of G4-H4 is 1 or more then the sum of G4-H4.

'=IF(SUM(G4-H4<=1),"",SUM(G4-H4))

If G4 has less than 1 or a fraction of 1 then i get the dreaded #VALUE!
thingy.

This seems to work UNLESS one of the cells feeding the formula in G4 is
empty
then i also the #VALUE! thingy.
=IF(H4="","",IF(H4<G4,G4-H4,0))

Any direction?
Click to expand...

Possibly either of the formulas in G4 or H4 is returning a text value rather
than a numeric value.

Post those formulas, and any necessary precedents, and things may be more
clear.


--ron
 
D

Desert Piranha

Ron said:
Possibly either of the formulas in G4 or H4 is returning a text value
rather
than a numeric value.

Post those formulas, and any necessary precedents, and things may be
more
clear.

--ronHi Ron,
Click to expand...
Thx for your reply.
G4 is '=IF(C3="","",(C3/D3/E3*F3)) can have a result less than 1. IE
(.0123)

H4 is user input of a whole number.
They are both formated as number with no decimals.

C,D,E,F have numbers and are formated as number.
 
W

wdjsxj

“Desert Piranhaâ€ç¼–写：
Hi,

In G4 i have the results of a formula which can be less than one.
In H4 there are whole numbers or a 0.

If the sum of G4-H4 is less than 1 then nothing.
If the sum of G4-H4 is 1 or more then the sum of G4-H4.

'=IF(SUM(G4-H4<=1),"",SUM(G4-H4))

If G4 has less than 1 or a fraction of 1 then i get the dreaded #VALUE!
thingy.

This seems to work UNLESS one of the cells feeding the formula in G4 is
empty
then i also the #VALUE! thingy.
=IF(H4="","",IF(H4<G4,G4-H4,0))

Any direction?
Click to expand...
your formula in G4 generate a empty value "",that is not numeric.
Is that the problem?
Wdjsxj
 
W

wdjsxj

Hi,
The formula in G4 must have generated an empty value "",that is not numeric.
Will that be the problem?
wdjsxj

“Desert Piranhaâ€ç¼–写：
 
R

Ron Rosenfeld

Thx for your reply.
G4 is '=IF(C3="","",(C3/D3/E3*F3)) can have a result less than 1. IE
(.0123)

H4 is user input of a whole number.
They are both formated as number with no decimals.

C,D,E,F have numbers and are formated as number.
Click to expand...

The problem arises when G4 = ""

Using "" with an arithmetic operator will result in a #VALUE! error.

In your original formula, the SUM function is redundant. Your formula:

=IF(SUM(G4-H4<=1),"",SUM(G4-H4))

is the same as:

=IF(G4-H4<=1,"",G4-H4)

and =""-n --> #VALUE!

However, the SUM function, properly used, is not limited in this way. So you
could rewrite your formula:

=IF(SUM(G4,-H4)<=1,"",G4-H4)

to avoid the error in the situation where G4 = ""




--ron
 
Top