Excel: An annoying little problem.

[CaptainFillosan]

Can't work it out! :mad:

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

[What_does_this_button_do?]

send me the file.

Email address is [email protected]

[PickyPerkins]

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! :)

[Tinstaafl]

Are all of the referenced cells formatted correctly ie as a number, not text?

[PickyPerkins]

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:
http://home.infi.net/~blueblue/_uimages/SS1.gif

If the A3 cell contains text with default formatting it looks like this and the sum is incorrect:
http://home.infi.net/~blueblue/_uimages/SS2.gif

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:
http://home.infi.net/~blueblue/_uimages/SS3.gif

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:
http://home.infi.net/~blueblue/_uimages/SS4.gif

As I said above, this is in Works. Perhaps someone else can check what happens in Excel. Have fun. :)

[CaptainFillosan]

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.

[Ausatco]

Luv your work, Picky. Well done

AA