software

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-H4lt;=1),quot;quot;,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=quot;quot;,quot;quot;,IF(H4lt;G4,G4-H4,0))

Any direction?--
Desert Piranha------------------------------------------------------------------------
Desert Piranha's Profile: www.excelforum.com/member.php...oamp;userid=28934
View this thread: www.excelforum.com/showthread...hreadid=519617On Mon, 6 Mar 2006 22:03:38 -0600, Desert Piranha
lt;Desert.Piranha.24a7am_1141704601.3763@excelforu m-nospam.comgt; wrote:

gt;
gt;Hi,
gt;
gt;In G4 i have the results of a formula which can be less than one.
gt;In H4 there are whole numbers or a 0.
gt;
gt;If the sum of G4-H4 is less than 1 then nothing.
gt;If the sum of G4-H4 is 1 or more then the sum of G4-H4.
gt;
gt;'=IF(SUM(G4-H4lt;=1),quot;quot;,SUM(G4-H4))
gt;
gt;If G4 has less than 1 or a fraction of 1 then i get the dreaded #VALUE!
gt;thingy.
gt;
gt;This seems to work UNLESS one of the cells feeding the formula in G4 is
gt;empty
gt;then i also the #VALUE! thingy.
gt;=IF(H4=quot;quot;,quot;quot;,IF(H4lt;G4,G4-H4,0))
gt;
gt;Any direction?

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


Ron Rosenfeld Wrote:
gt; On Mon, 6 Mar 2006 22:03:38 -0600, Desert Piranha
gt; lt;Desert.Piranha.24a7am_1141704601.3763@excelforu m-nospam.comgt; wrote:
gt;
gt; gt;
gt; gt;Hi,
gt; gt;
gt; gt;In G4 i have the results of a formula which can be less than one.
gt; gt;In H4 there are whole numbers or a 0.
gt; gt;
gt; gt;If the sum of G4-H4 is less than 1 then nothing.
gt; gt;If the sum of G4-H4 is 1 or more then the sum of G4-H4.
gt; gt;
gt; gt;'=IF(SUM(G4-H4lt;=1),quot;quot;,SUM(G4-H4))
gt; gt;
gt; gt;If G4 has less than 1 or a fraction of 1 then i get the dreaded
gt; #VALUE!
gt; gt;thingy.
gt; gt;
gt; gt;This seems to work UNLESS one of the cells feeding the formula in G4
gt; is
gt; gt;empty
gt; gt;then i also the #VALUE! thingy.
gt; gt;=IF(H4=quot;quot;,quot;quot;,IF(H4lt;G4,G4-H4,0))
gt; gt;
gt; gt;Any direction?
gt;
gt; Possibly either of the formulas in G4 or H4 is returning a text value
gt; rather
gt; than a numeric value.
gt;
gt; Post those formulas, and any necessary precedents, and things may be
gt; more
gt; clear.
gt;
gt; --ronHi Ron,
Thx for your reply.
G4 is '=IF(C3=quot;quot;,quot;quot;,(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.--
Desert Piranha------------------------------------------------------------------------
Desert Piranha's Profile: www.excelforum.com/member.php...oamp;userid=28934
View this thread: www.excelforum.com/showthread...hreadid=519617
“Desert Piranha”编写：

gt;
gt; Hi,
gt;
gt; In G4 i have the results of a formula which can be less than one.
gt; In H4 there are whole numbers or a 0.
gt;
gt; If the sum of G4-H4 is less than 1 then nothing.
gt; If the sum of G4-H4 is 1 or more then the sum of G4-H4.
gt;
gt; '=IF(SUM(G4-H4lt;=1),quot;quot;,SUM(G4-H4))
gt;
gt; If G4 has less than 1 or a fraction of 1 then i get the dreaded #VALUE!
gt; thingy.
gt;
gt; This seems to work UNLESS one of the cells feeding the formula in G4 is
gt; empty
gt; then i also the #VALUE! thingy.
gt; =IF(H4=quot;quot;,quot;quot;,IF(H4lt;G4,G4-H4,0))
gt;
gt; Any direction?
gt;
gt;
gt; --
gt; Desert Piranha
gt;
gt; Hi,
your formula in G4 generate a empty value quot;quot;,that is not numeric.
Is that the problem?
Wdjsxj
gt; ------------------------------------------------------------------------
gt; Desert Piranha's Profile: www.excelforum.com/member.php...oamp;userid=28934
gt; View this thread: www.excelforum.com/showthread...hreadid=519617
gt;
gt;

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

“Desert Piranha”编写：

gt;
gt; Hi,
gt;
gt; In G4 i have the results of a formula which can be less than one.
gt; In H4 there are whole numbers or a 0.
gt;
gt; If the sum of G4-H4 is less than 1 then nothing.
gt; If the sum of G4-H4 is 1 or more then the sum of G4-H4.
gt;
gt; '=IF(SUM(G4-H4lt;=1),quot;quot;,SUM(G4-H4))
gt;
gt; If G4 has less than 1 or a fraction of 1 then i get the dreaded #VALUE!
gt; thingy.
gt;
gt; This seems to work UNLESS one of the cells feeding the formula in G4 is
gt; empty
gt; then i also the #VALUE! thingy.
gt; =IF(H4=quot;quot;,quot;quot;,IF(H4lt;G4,G4-H4,0))
gt;
gt; Any direction?
gt;
gt;
gt; --
gt; Desert Piranha
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Desert Piranha's Profile: www.excelforum.com/member.php...oamp;userid=28934
gt; View this thread: www.excelforum.com/showthread...hreadid=519617
gt;
gt;

On Mon, 6 Mar 2006 22:35:31 -0600, Desert Piranha
lt;Desert.Piranha.24a8om_1141706400.7828@excelforu m-nospam.comgt; wrote:

gt;
gt;Ron Rosenfeld Wrote:
gt;gt; On Mon, 6 Mar 2006 22:03:38 -0600, Desert Piranha
gt;gt; lt;Desert.Piranha.24a7am_1141704601.3763@excelforu m-nospam.comgt; wrote:
gt;gt;
gt;gt; gt;
gt;gt; gt;Hi,
gt;gt; gt;
gt;gt; gt;In G4 i have the results of a formula which can be less than one.
gt;gt; gt;In H4 there are whole numbers or a 0.
gt;gt; gt;
gt;gt; gt;If the sum of G4-H4 is less than 1 then nothing.
gt;gt; gt;If the sum of G4-H4 is 1 or more then the sum of G4-H4.
gt;gt; gt;
gt;gt; gt;'=IF(SUM(G4-H4lt;=1),quot;quot;,SUM(G4-H4))
gt;gt; gt;
gt;gt; gt;If G4 has less than 1 or a fraction of 1 then i get the dreaded
gt;gt; #VALUE!
gt;gt; gt;thingy.
gt;gt; gt;
gt;gt; gt;This seems to work UNLESS one of the cells feeding the formula in G4
gt;gt; is
gt;gt; gt;empty
gt;gt; gt;then i also the #VALUE! thingy.
gt;gt; gt;=IF(H4=quot;quot;,quot;quot;,IF(H4lt;G4,G4-H4,0))
gt;gt; gt;
gt;gt; gt;Any direction?
gt;gt;
gt;gt; Possibly either of the formulas in G4 or H4 is returning a text value
gt;gt; rather
gt;gt; than a numeric value.
gt;gt;
gt;gt; Post those formulas, and any necessary precedents, and things may be
gt;gt; more
gt;gt; clear.
gt;gt;
gt;gt; --ronHi Ron,
gt;Thx for your reply.
gt;G4 is '=IF(C3=quot;quot;,quot;quot;,(C3/D3/E3*F3)) can have a result less than 1. IE
gt;(.0123)
gt;
gt;H4 is user input of a whole number.
gt;They are both formated as number with no decimals.
gt;
gt;C,D,E,F have numbers and are formated as number.

The problem arises when G4 = quot;quot;

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

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

=IF(SUM(G4-H4lt;=1),quot;quot;,SUM(G4-H4))

is the same as:

=IF(G4-H4lt;=1,quot;quot;,G4-H4)

and =quot;quot;-n --gt; #VALUE!

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

=IF(SUM(G4,-H4)lt;=1,quot;quot;,G4-H4)

to avoid the error in the situation where G4 = quot;quot;

--ron