I'm downloading data from a database into Excel; some of the cells have $0
values, but some are just blank. If I sum a row or a column which includes a
blank cell, I get an error message: #VALUE! How can I convert the blank
cells to zeros?
--
karen

Hi Karen,

Blank cells don't give #VALUE when summed, cells with spaces do.
You can check with the LEN() function; if the result is not zero, there's something in the cell.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"karen" wrote in message ...
| I'm downloading data from a database into Excel; some of the cells have $0
| values, but some are just blank. If I sum a row or a column which includes a
| blank cell, I get an error message: #VALUE! How can I convert the blank
| cells to zeros?
| --
| karen

<Blank cells don't give #VALUE when summed, cells with spaces do.

Not entirely true; the SUM() functions ignores spaces, individual additions don't.
So don't rely on that, always make sure values to be added are numeric

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| Hi Karen,
|
| Blank cells don't give #VALUE when summed, cells with spaces do.
| You can check with the LEN() function; if the result is not zero, there's something in the cell.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "karen" wrote in message ...
|| I'm downloading data from a database into Excel; some of the cells have $0
|| values, but some are just blank. If I sum a row or a column which includes a
|| blank cell, I get an error message: #VALUE! How can I convert the blank
|| cells to zeros?
|| --
|| karen
|
|

Thank you. I performed the function, and a 0 appeared. But even then I'm
getting the #VALUE! result.
--
karen


"Niek Otten" wrote:

Hi Karen,

Blank cells don't give #VALUE when summed, cells with spaces do.
You can check with the LEN() function; if the result is not zero, there's something in the cell.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"karen" wrote in message ...
| I'm downloading data from a database into Excel; some of the cells have $0
| values, but some are just blank. If I sum a row or a column which includes a
| blank cell, I get an error message: #VALUE! How can I convert the blank
| cells to zeros?
| --
| karen

Okay, how do I fill the blank cells with zero values other than manually?

thanks!
--
karen


"Niek Otten" wrote:

<Blank cells don't give #VALUE when summed, cells with spaces do.

Not entirely true; the SUM() functions ignores spaces, individual additions don't.
So don't rely on that, always make sure values to be added are numeric

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| Hi Karen,
|
| Blank cells don't give #VALUE when summed, cells with spaces do.
| You can check with the LEN() function; if the result is not zero, there's something in the cell.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "karen" wrote in message ...
|| I'm downloading data from a database into Excel; some of the cells have $0
|| values, but some are just blank. If I sum a row or a column which includes a
|| blank cell, I get an error message: #VALUE! How can I convert the blank
|| cells to zeros?
|| --
|| karen
|
|

SUM ignores blanks and text so you have something else happening.

Post the actual formula you are using and a small sample of data.


Gord Dibben MS Excel MVP

On Fri, 11 Jan 2008 09:20:01 -0800, karen
wrote:

I'm downloading data from a database into Excel; some of the cells have $0
values, but some are just blank. If I sum a row or a column which includes a
blank cell, I get an error message: #VALUE! How can I convert the blank
cells to zeros?

If =sum() returns 0, then the cells with those numbers aren't really numbers.

You could try converting from text to numbers by:
select an empty cell
edit|copy
select the offending range
edit|paste special|check Add

If the =sum() doesn't change, maybe you got the data from a web site and your
values have those HTML non-breaking spaces in them.

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

karen wrote:

I'm downloading data from a database into Excel; some of the cells have $0
values, but some are just blank. If I sum a row or a column which includes a
blank cell, I get an error message: #VALUE! How can I convert the blank
cells to zeros?
--
karen

--

Dave Peterson