Excel If Statement

July 14, 2009 at 18:12:35
Specs: Windows XP
I have a column that has both blank cells and cells populated with amounts, there are no formulas in any of the cells. I want to change the blank cells to 0, and do nothing with the cells that contain an amount, I want the amount to remain unchanged. I've tried =IF(ISBLANK(A1),"0",IF(ISNUMBER(A1),A1)), this puts the 0 I want in the blank cell, but also replaces my amount with a 0, when I want it to remain the original amount. Can you help me with my IF statement?

See More: Excel If Statement

Report •

July 14, 2009 at 21:10:06
Try this instead of an =IF statement:

Select / Highlight all the cells in the target column
On the Task Bar,
Select Edit
Select Go To
Click the Special button at the bottom
Select Blanks
Click OK
Now type an equal sign and zero =0 DO NOT PRESS ENTER YET
Next, hold Ctrl key and press Enter

That should do it.



Report •

July 15, 2009 at 06:05:21
Mike's method will indeed work, but there a couple of subtleties to keep in mind.

1 - I believe that using =0 will cause Excel to recalculate the cells just like all the other formulae. In most cases this probably won't matter, but in a large spreadsheet with a large number of formulae, it could slow it down.

2 - When using the Tools...Options...Formula checkbox, these cells will show up as formulae. Probably not a big deal, but worth mentioning.

A method that avoids these issues is:

1 - Put a 0 in one of your blank cells
2 - Copy the cell (Ctrl-c or right-click...Copy)
3 - Select your range
4 - Use Edit...Paste Special...Operation: Add

Of course, both of these methods require that you have the Tools..Options...Zero Values option selected.

Report •

Related Solutions

Ask Question