"#VALUE" error from IF statement

[derohanes]

This works all the time with a new worksheet, but as I keep editing cells something happens. Say, I want to put a blank in cell B1 if A1 is blank. In B1 I use =IF(A1="","",A1). If I try to use B1 in a formula it treats it as a "0" and calculates fine. Sometimes though I'll get a "#VALUE" error in a cell. Meanwhile, the cells above it and below it in a column are acting fine. Something has happened to that cell's format. I've tried to format it as a number cell, and a general cell. Neither works. Can someone help with this issue?

 

[AntMac]

Keep in mind when you format a cell as a number, that's just the display of it. What I have found is easier for this situation is to make the formula if(A1="",0,A1) then use conditional formatting to make the font color white (or match the background color) so it appears to your eyes like it's blank and then it magically fills in when A1 is populated.

 

[AntMac]

Keep in mind when you format a cell as a number, that's just the display of it. What I have found is easier for this situation is to make the formula if(A1="",0,A1) then use conditional formatting to make the font color white (or match the background color) so it appears to your eyes like it's blank and then it magically fills in when A1 is populated.

To explain a little more when you put "" you are telling Excel to put a text entry there. Even though you format the cell to display as a number, when it tries to do calculations those are still going to be on the text value. So, when it tries to do number math to a text value it gets confused. So, if you have it put in a 0 that will satisfy the math parts. Then use conditional formatting that when this value is 0, or you can even have the conditional formatting be if A1 is blank, then change the font color so that it's just as effective as being blank to the user's eyes.