#value!
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? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=519617 |
#value!
On Mon, 6 Mar 2006 22:03:38 -0600, Desert Piranha
<Desert.Piranha.24a7am_1141704601.3763@excelforu m-nospam.com wrote: 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? 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 |
#value!
Ron Rosenfeld Wrote: On Mon, 6 Mar 2006 22:03:38 -0600, Desert Piranha <Desert.Piranha.24a7am_1141704601.3763@excelforu m-nospam.com wrote: 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? 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, 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. -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=519617 |
#value!
€œ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? -- Desert Piranha Hi, your formula in G4 generate a empty value "",that is not numeric. Is that the problem? Wdjsxj ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=519617 |
#value!
Hi,
The formula in G4 must have generated an empty value "",that is not numeric. Will that be the problem? 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? -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=519617 |
#value!
On Mon, 6 Mar 2006 22:35:31 -0600, Desert Piranha
<Desert.Piranha.24a8om_1141706400.7828@excelforu m-nospam.com wrote: Ron Rosenfeld Wrote: On Mon, 6 Mar 2006 22:03:38 -0600, Desert Piranha <Desert.Piranha.24a7am_1141704601.3763@excelforu m-nospam.com wrote: 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? 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, 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. 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 |
All times are GMT +1. The time now is 11:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com