Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error in SUM function ? (Excel 97)

    Hi all,

    I have found something strange : I have a workbook with a lot of sheets (but there are always 2 sheets per month). One monthly sheet with data and one with a graph (related to the corresponding data of that month). Every month I copy both sheets (in the same workbook) to have new ones, then I just change the TAB, enter the correct data for that month and then set the source of the graph on the corresponding (copied) sheet to the correct data sheet. I hope I am a bit clear here.

    I just did this for the past month and noticed something very strange : Excel makes a wrong calculation when using the SUM-function.
    I copied the cells involved to a new workbook to illustrate this.

    I have set the cell properties to the Number-format with 0 decimals, but this changes nothing.

    Most probably I am doing something wrong, so that Excel gives the wrong solution, butI would like to know what I'm doing in error. One could easily overlook this and make decisions based on wrong data.

    Walter

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Error in SUM function ? (Excel 97)

    The value in A2 is being treated by Exel as Text.

    If you copy a blank cell, Select all the values and then PasteSpecial, choose the Add operator and the problem should be rectified.

    Andrew C

  3. #3
    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: Error in SUM function ? (Excel 97)

    The "1" in A2 is not a number it is TEXT.

    A couple of hints to see this
    If you change the format to show more decimals you will see that this "number" does not increase in decimals.

    Also you changed this cell (and only this one) to be right-aligned. All the others have no alignment. If you would leave them "unaligned" explicitly, numbers are on the right and text is on the left

    To fix a column of numbers that have text (that looks like numbers) and numbers, highlight the column of data, data -text to columns - delimited <Finish> and they will be converted to numbers

    Ot you can copy a 1 from a cell, select the range and then paste-special multiply (or copy 0 and paste special - add)

    Steve

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

    Re: Error in SUM function ? (Excel 97)

    Cell A2 looks like a number, but it contains text. This often happens when data are imported from another application. To make sure that all cells in a range are interpreted as numbers (if appropriate), there are several methods:
    - Select the cells, then Data | Text to Columns, Delimited, Finish.
    or
    - Select a blank cell, copy it (Ctrl+C), select the cells in question, Edit | Paste Special, Add option, OK.

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

    Re: Error in SUM function ? (Excel 97)

    Where is the "piling on" smilie? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  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: Error in SUM function ? (Excel 97)

    There is nothing like good advice in triplicate! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error in SUM function ? (Excel 97)

    Thank you, Andrew !

    I felt it had to be related to a cell being text but looking as a number, therefore I selected the range and set the "Format" to "Number". But this obviously did not change this A2-cell. I wonder why ?

    Anyway, I do now know to be very careful when copying cells!

    Best regards,

    Walter

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error in SUM function ? (Excel 97)

    Thanks Steve (and you, Hans) for your hints on how to check if a (range of ) cell(s) looks like a number, but really is treated as text.

    Best regards,

    Walter

Posting Permissions

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