Trying to sum column but getting 0 as result
 

I've copied information into an excel spreadsheet, and I'm trying to sum
certain columns, however my formula is returning the result of "0". The
column I'm trying to total has numbers such as 2.0, 16.0, etc. so I'm
thinking it has something to do with the formatting in these cells,
however I can't figure out what to do to make it work. Can someone
please help!


--
telewats
------------------------------------------------------------------------
telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

The cells are probably text, although they look like numbers and you
formatted them as numbers after pasting them.
Format an empty cell as Number. Enter the number 1. EditCopy. Select your
"numbers". EditPaste special, check Multiply

--
Kind regards,

Niek Otten

"telewats" wrote in
message ...

I've copied information into an excel spreadsheet, and I'm trying to sum
certain columns, however my formula is returning the result of "0". The
column I'm trying to total has numbers such as 2.0, 16.0, etc. so I'm
thinking it has something to do with the formatting in these cells,
however I can't figure out what to do to make it work. Can someone
please help!


--
telewats
------------------------------------------------------------------------
telewats's Profile:
http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

It probably is due to the formatting, they are probably all formatted as
text. To fix:

Copy and empty cell.
Select your range and do a "Paste Special" - "Add" to convert them to
numbers.

Does that help?
--
Regards,
Dave


"telewats" wrote:


I've copied information into an excel spreadsheet, and I'm trying to sum
certain columns, however my formula is returning the result of "0". The
column I'm trying to total has numbers such as 2.0, 16.0, etc. so I'm
thinking it has something to do with the formatting in these cells,
however I can't figure out what to do to make it work. Can someone
please help!


--
telewats
------------------------------------------------------------------------
telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

I've tried both suggestions posted, however neither is working. After
trying the suggestions, I checked the formatting for the cells in
question, and they show to be number formatted, however I still get 0
when I sum them.


--
telewats
------------------------------------------------------------------------
telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

The formatting doesn't matter. Have you tried the ISNUMBER() function? I'm
pretty sure that's the problem.
ToolsOptionsCalculation, Automatic checked?

--
Kind regards,

Niek Otten

"telewats" wrote in
message ...

I've tried both suggestions posted, however neither is working. After
trying the suggestions, I checked the formatting for the cells in
question, and they show to be number formatted, however I still get 0
when I sum them.


--
telewats
------------------------------------------------------------------------
telewats's Profile:
http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

You don't happen to have set a comma as decimal separator instead of a
period?

--
Kind regards,

Niek Otten

"telewats" wrote in
message ...

I've tried both suggestions posted, however neither is working. After
trying the suggestions, I checked the formatting for the cells in
question, and they show to be number formatted, however I still get 0
when I sum them.


--
telewats
------------------------------------------------------------------------
telewats's Profile:
http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

The ISNUMBER() didn't work, and I've double checked the comma; all is
okay there. Just a note, I've imported this data from a online
cellphone bill, and I'm trying to total minutes by phone number. Could
there be a problem because of the format of the other doc?


--
telewats
------------------------------------------------------------------------
telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

What do you mean "didn't work"? Did it say FALSE?

Check with the LEN function for the correct length of the number (or
actually the text, I'm sure); there may be non-printable characters in the
cell. Check the CLEAN() and TRIM() functions.

--
Kind regards,

Niek Otten

"telewats" wrote in
message ...

The ISNUMBER() didn't work, and I've double checked the comma; all is
okay there. Just a note, I've imported this data from a online
cellphone bill, and I'm trying to total minutes by phone number. Could
there be a problem because of the format of the other doc?


--
telewats
------------------------------------------------------------------------
telewats's Profile:
http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

The extra spaces is definitely the problem. I've checked LEN, and I
actually have 6-7 spaces in these fields instead of the 3-4 of actual
data. I guess I'm just not proficient enough with Excel to fix this,
though, because I can't get rid of the extra spaces. I've tried CLEAN
and TRIM, but neither is removing the spaces...?


--
telewats
------------------------------------------------------------------------
telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

I was finally able to correct this issue, and I just thought I'd let
everyone know how in case you ever run into this yourself (of course,
your probably smarter than I am and already know how to do this!)

I use =CODE(RIGHT(XX,1)) to determine the numeric code for the last
character in my string. This brought back char(160), which =TRIM and
=CLEAN won't eliminate.

I then highlighted one of the cells, pressed F2 then SHIFT+Left Arrow
to highlight the spaces I needed to remove. I copied these to the
clipboard.

I then highlighted all the cells I needed changed, and used
EDIT/REPLACE, and pasted (CTRL-V) the copied info and left "Replace
With" blank. I chose Replace All.

I am now able to sum all the fields as needed!

Thanks to everyone that posted info for me --- I couldn't have found
this result if it hadn't been for you.


--
telewats
------------------------------------------------------------------------
telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=500378

Trying to sum column but getting 0 as result
 

Thanks for the update on how you resolved your problem.

May help others in future.

David McRitchie has a TRIMALL macro that looks for the non-breaking space (160)
along with other crap in cells.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


Gord Dibben MS Excel MVP

On Fri, 13 Jan 2006 10:42:18 -0600, telewats
wrote:


I was finally able to correct this issue, and I just thought I'd let
everyone know how in case you ever run into this yourself (of course,
your probably smarter than I am and already know how to do this!)

I use =CODE(RIGHT(XX,1)) to determine the numeric code for the last
character in my string. This brought back char(160), which =TRIM and
=CLEAN won't eliminate.

I then highlighted one of the cells, pressed F2 then SHIFT+Left Arrow
to highlight the spaces I needed to remove. I copied these to the
clipboard.

I then highlighted all the cells I needed changed, and used
EDIT/REPLACE, and pasted (CTRL-V) the copied info and left "Replace
With" blank. I chose Replace All.

I am now able to sum all the fields as needed!

Thanks to everyone that posted info for me --- I couldn't have found
this result if it hadn't been for you.