Results 1 to 8 of 8

20040802, 13:58 #1
 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 SUMfunction.
I copied the cells involved to a new workbook to illustrate this.
I have set the cell properties to the Numberformat 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

20040802, 14:08 #2
 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

20040802, 14:10 #3
 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 rightaligned. 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 pastespecial multiply (or copy 0 and paste special  add)
Steve

20040802, 14:10 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20040802, 14:11 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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>

20040802, 14:13 #6
 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

20040802, 15:48 #7
 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 A2cell. I wonder why ?
Anyway, I do now know to be very careful when copying cells!
Best regards,
Walter

20040802, 15:50 #8
 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