how to get a formmula to return a 0??
Posted by scott on December 12, 2001 6:41 PM
I have the formula I need, a nested IF. My only problem is if the result is false, I need it to either place a 0 in the result cell, OR I need it to leave the cell blank.
Currently I am getting a #VALUE return.
My formula is as follows;
IF(I8>0,SUM(H8-I8),"")
Ive also tried
IF (I8>0,SUM(H8-I8),"0")
Ive also tried
IF (I8>0,SUM(H8-I8))
none of them reutn a 0 or leave the cell blank.
Please help
Thnx amillion
scott
Posted by Juan Pablo G. on December 12, 2001 6:52 PM
First, did you see the post from Joe Was or mine ?
Second, there's no need to use SUM there, because that's only one argument.
you can replace
SUM(H8-I8)
with
H8-I8
Juan Pablo G.
Posted by scott on December 12, 2001 6:59 PM
Yes, I was afk, came back and posted, then saw your replies after, thnx.
OK, so is there any way to get rid of that #VALUE! that keeps coming up? The formulas you gave worked fine but when I tried to sum the difference column with a #VALUE! in it I get the same message in the total cell. Thats what im trying to work around.
All help would be appreciated
Posted by Juan Pablo G. on December 12, 2001 7:01 PM
I don't understand why are you getting a VALUE error...every cell should be either 0 or the difference... select a cell that has the error, and press Shift F3 (To bring up the Paste Function command), you should see the IF Function with its three arguments, which one is returning a #VALUE! error ?
Juan Pablo G.
Posted by scott on December 12, 2001 7:48 PM
The second portion
value_if_true (H4-I4) =#VALUE!
This is the current formula:
IF(I4>0,H4-I4),"")
If you use another formula you suggested I get two errors
The first portion
I8
The second portion
I8-H8
For the formula:
IF(I8,I8-H8,0)
Posted by Juan Pablo G. on December 12, 2001 7:56 PM
Well, i agree with the others, your column B is Text. Select it, go to Data, Text to Columns, select Fixed With and click Finish. They should be now REAL numbers and the error must be gone.
Juan Pablo G.
Posted by scott on December 12, 2001 8:02 PM
You all FUDGIN ROCK!!!!!!
Im completely serious, this is all being done as a personal project thats going to be presented to our VP.
You made my day, and possibly my future(: Thnx a million.
