Excel: An annoying little problem.
Thread Starter
Joined: Sep 2002
Posts: 171
Likes: 0
From: UK
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
Joined: Feb 2000
Posts: 776
Likes: 0
From: [edited by PPRuNe Admin]
Joined: Jul 2002
Posts: 233
Likes: 0
From: 40N, 80W
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 October 2002 at 02:28.
Joined: Jul 2002
Posts: 233
Likes: 0
From: 40N, 80W
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.
