Skip to main content
No. of Recommendations: 0
I was doing a very basic task, went to my Credit Union website, and Copied the summaries of the various accounts we keep there, water to see the total amount, so I simply pasted into an Excel sheet, selected the $xxxx.xx amounts, moved them into their own column, went down a cell, hit the AutoSUM button. It didn't select the amounts above, and returned a 0. Changed format to Currency, Accounting, same thing.. round and round..

Searched the function via Google, nothing came to mind... OK, took it over to Apple Numbers, and there I got the SUM OK.... So back to Excel...

Finally I noticed the pasted Currency amounts all had a space after the amount, one space... As I removed that space in each row, the SUM grew to finally to actual SUM..

Sneaky, crazy error, unexpected, but a lesson learned.. Watch that SPACE!
Print the post Back To Top
No. of Recommendations: 0
wecoguy,

I will guess that when you pasted them, the numbers were all on the left side of the cell, correct?

That is an indicator that something is wrong with the data. Leading or trailing spaces or tabs make it non-numeric.

Some Excel versions also have issues when a pasted number has a comma or dollar sign in it.

Numbers should always land on the right side of the cell.


Gene
All holdings and some statistics on my Fool profile page
http://my.fool.com/profile/gdett2/info.aspx
Print the post Back To Top
No. of Recommendations: 1
Hi Gene,

No, they were all neatly right justified, but as text initially, so I changed the items of interest to currency, then was mystified that the AutoSUM just didn't work.. Began to blame Excel, but the added spaces were small, unnoticed.. As I said, a lesson learned..

weco
Print the post Back To Top
No. of Recommendations: 1
If you end up with a lot of these, then Excel's text-to-columns feature will fix a large number at one shot
Print the post Back To Top
No. of Recommendations: 1
Just a one time shot, I think, but good to keep that in mind! Thanks!
Print the post Back To Top