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!
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. Edit>Copy. Select your
"numbers". Edit>Paste special, check Multiply
--
Kind regards,
Niek Otten
"telewats" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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
>
>
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.
The formatting doesn't matter. Have you tried the ISNUMBER() function? I'm
pretty sure that's the problem.
Tools>Options>Calculation, Automatic checked?
--
Kind regards,
Niek Otten
"telewats" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
You don't happen to have set a comma as decimal separator instead of a
period?
--
Kind regards,
Niek Otten
"telewats" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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?
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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...?
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.
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
<[email protected]> 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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks