MSExcel: Convert blank cells to 0? 1

[BaudKarma]

I've got a largish spreadsheet that I imported from Access. I'm using the values in the spreadsheet to calculated averages on another worksheet. The problem is that the imported data has a large number of blank cells. I want to convert those cells to 0 when I use them to calculate the averages.

I tried using =If(isblank(A1),0,A1), thinking I could just paste that formula into every cell and it could convert the blanks and ignore the rest. However, this formula wants to covert every cell to 0, whether it is blank or not.

 

[AnotherHiggins]

Using that formula should work. It only replaces empty cells with zeros, not all cells.

But I'd just use the ol' "Time 1 Fix" instead of bothering with a formula at all. This is a tactic to convert imported, text-formatted numbers to actual numbers in Excel, but it will also convert any blank cell to a zero (which is your goal).

[ul][li]In a cell to the right of all your used cells, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the entire dataset, blanks included[/li]
[li]Go to Edit > Paste Special then tick the boxes beside Multiply and Value[/li][/ul]

All blank cells will be converted to zeros. All number cells will remain unaffected since X * 1 = X. And all Text cells will remain unaffected since you can't multiply a text string.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[BaudKarma]

Nifty trick, but it doesn't work for me. However, when I click an individual blank cell and do the "Clear contents" thing, then reran the paste special, the cell coverted to 0 without a problem. I guess there's just something funky about these cells.

I did a little poking around, found the ISNUMBER function. If(Isnumber(A1),A1,0) coverted everything, number or not, to 0. I finally made a copy of my entire worksheet, and did a (If(Isnumber(worksheet2!A1),worksheet2!A1,0). That converted all the blanks to zeros and left the numbers alone.