Excel: An annoying little problem.
Thread Starter
Join Date: Sep 2002
Location: UK
Posts: 171
Likes: 0
Received 0 Likes
on
0 Posts
Excel: An annoying little problem.
Can't work it out! 
In using the SUM formulae.
I am using rows 1 to 60 inclusive. Each item has a value of £30 - the total sum therefore should be £1800 - but it isn't - it is £1770 with the number of items certainly totalling 60!
What can be the problem? Anyone help? Or am I just thick?
The formula I am using is =SUM(D1: D60) I have spaced it because the colon and D make a smilie- how sweet!
Thanks
In using the SUM formulae.
I am using rows 1 to 60 inclusive. Each item has a value of £30 - the total sum therefore should be £1800 - but it isn't - it is £1770 with the number of items certainly totalling 60!
What can be the problem? Anyone help? Or am I just thick?
The formula I am using is =SUM(D1: D60) I have spaced it because the colon and D make a smilie- how sweet!
Thanks
Join Date: Feb 2000
Location: [edited by PPRuNe Admin]
Posts: 776
Likes: 0
Received 0 Likes
on
0 Posts
Join Date: Jul 2002
Location: 40N, 80W
Posts: 233
Likes: 0
Received 0 Likes
on
0 Posts
Could one of your 30's be a character 30 and the rest numeric 30's?
The SUM() function would still work, but the character 30 would count as a zero.
I don't have Excel on the computer I am using at the moment, but in Microsoft Works, for instance, numeric values are aligned right by default, and text characters are aligned left by default, so one can detect this kind of error at a glance.
Of course, one CAN align all the cells the same way and hence use technology to shoot oneself (or somebody else) in the foot if you want to.
Hope this helps!
The SUM() function would still work, but the character 30 would count as a zero.
I don't have Excel on the computer I am using at the moment, but in Microsoft Works, for instance, numeric values are aligned right by default, and text characters are aligned left by default, so one can detect this kind of error at a glance.
Of course, one CAN align all the cells the same way and hence use technology to shoot oneself (or somebody else) in the foot if you want to.
Hope this helps!
Last edited by PickyPerkins; 28th Oct 2002 at 02:28.
Join Date: Jul 2002
Location: 40N, 80W
Posts: 233
Likes: 0
Received 0 Likes
on
0 Posts
On looking at Works again, I see that text characters aligned right (by reformatting) are distinguishable from numeric values (aligned right by default) by the absence of a space to the right of the text characters. So text can normally be detected at a glance even when reformatted right.
______________________________
To summarize:
If all the cells contain numerics the spreadsheet looks like this with a space to the right of the numbers and the sum is correct:
If the A3 cell contains text with default formatting it looks like this and the sum is incorrect:
If the text in A3 is then manually aligned right it looks look like this with no space the right and the sum is still incorrect:
Now of course if you really want to shoot yourself (or somebody else) in the foot you can add a space to the right of the text so that everything lines up nicely. Everthing then looks right but the sum is wrong:
As I said above, this is in Works. Perhaps someone else can check what happens in Excel. Have fun.
______________________________
To summarize:
If all the cells contain numerics the spreadsheet looks like this with a space to the right of the numbers and the sum is correct:
If the A3 cell contains text with default formatting it looks like this and the sum is incorrect:
If the text in A3 is then manually aligned right it looks look like this with no space the right and the sum is still incorrect:
Now of course if you really want to shoot yourself (or somebody else) in the foot you can add a space to the right of the text so that everything lines up nicely. Everthing then looks right but the sum is wrong:
As I said above, this is in Works. Perhaps someone else can check what happens in Excel. Have fun.
Thread Starter
Join Date: Sep 2002
Location: UK
Posts: 171
Likes: 0
Received 0 Likes
on
0 Posts
Thanks very much indeed. From the answers it was possible to follow it through easily.
All cells were thus 30.00 BUT.............one cell was 30-00.
Bingo! Problem solved.
Thanks again.
All cells were thus 30.00 BUT.............one cell was 30-00.
Bingo! Problem solved.
Thanks again.
