MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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;

Ive also tried

IF (I8>0,SUM(H8-I8),"")

Ive also tried

IF (I8>0,SUM(H8-I8))

none of them reutn a 0 or leave the cell blank.

Please help
Thnx amillion

Posted by Walter on December 12, 2001 7:10 PM

All formula you tried should work if the content of both cells are numeric data. Did you check whether the value inside H8 and I8 are both numeric data?

PS:Cell format cannot tell you the format of the cell content. If you can increase/decrease the no. of decimal placed displayed, the cell contect is numeric data, otherwise, the cell content should be text.

Posted by Tom Dickinson on December 12, 2001 7:14 PM

I would say that the other part of your nested "if" is giving you the problem. The formula you have written here provides a null answer; you could substitute the number zero in place of the double quotes.

Posted by Sam on January 03, 2002 4:48 PM

I also have this problem especially where one of the cells in the formula is another formula containing formula referencing other worksheets. Found that the cells must contain data even if it is only 0 (number) to get rid of the #Value.