Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUM function don't work (Excel 2000)

    Hello all, i got a strange document where the sum function don't work. When i type new numbers it's not a problem but i can sum the numbers already in the document....
    document is attached

    Regards Gerwin

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function don't work (Excel 2000)

    They're not numbers (on my system) - they're right justified with thousand and decimal separators that are different from the ones I use.
    I presume they can be fixed by replacing all "." with "" and then the "," by "."

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function don't work (Excel 2000)

    No, that's not it. I've i do format cells and than numbers it also not work. And the sum function is working with other documents with , and.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUM function don't work (Excel 2000)

    When I do the replacements as suggested by Andrew the SUM works fine in the file.

    SUM only sums numbers. The "numbers" are text that only look like numbers. Change them to numbers and use formatting to format the numbers with the thousand separators and decimal separators and the numbers will stay as numbers and the sum will work

    Steve

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function don't work (Excel 2000)

    OK i've made numbers from it and save it agen and take a look. The sum of all this numbers is 0.00 that's not correct
    see attachment

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUM function don't work (Excel 2000)

    They are not numbers.

    See my attached with the numbers.

    If you enter a "thousands" separator manually, excel will make them text. If you format to include it excel will add it.

    Steve

  7. #7
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUM function don't work (Excel 2000)

    Sorry, it works correct!!! thanks allot! how can i see that it where fake "." ???

    Regard gerwin

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUM function don't work (Excel 2000)

    If the cell contains a number, you will see the number stripped of its formatting in the formula bar, i.e. without thousands separator.
    If the cell contains a text value that looks like a number, you will see the text "as is" in the formula bar, including the thousands separator.

    You can also select a range of cells and set the horizontal alignment to General (in the Alignment tab of Format | Cells...). True numbers will be right-aligned, text values will be left-aligned.

    There are several ways to convert text values that look like numbers to real numbers, for example:
    - Select a series of cells in a single column. Select Data | Text to Columns... and click Finish.
    - Select a blank cell and copy it to the clipboard (Ctrl+C). Select a range of cells (may be more than one column), then select Edit | Paste Special... Click the Add option, and click OK.
    - Excel 2002 and up have smart tags to handle this.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUM function don't work (Excel 2000)

    If you select a cell (or cells) you can see if they are "justified" (in this case the cells are "right justified"). If there is no "set justification" (you can "reselect" the justification to remove it), then text is left aligned and numbers are right aligned.

    If you had removed the justification, you could see that the "numbers" were actually "text that looks like a number" since it would have left justification.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •