| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert number into words | Excel Discussion (Misc queries) | |||
| How to .. | Excel Discussion (Misc queries) | |||
| Formula to lookup Multiple Column Text and then Count Result | Excel Worksheet Functions | |||
| I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
| Convert Numeric into Text | Excel Worksheet Functions | |||
Linear Mode
