Excel spreadsheet problem

[paulasch]

I'm at a loss. I use a private point of sale program that I can save specific data to an excel spreadsheet, recently (and I haven't a clue why) when I save the data as I alway have in the *.xls format. The cells which contain a three columns of date; dates, invoice numbers and dollar values the dollar values can't be added. I've formated the cells as everything I can think of, I've checked to make sure the data isn't protected and if I copy those number to another spot they can't be added either its like the data is text but the formatting is changed I can't figure it out. Anyone want to take a crack at this I can email you the sheet it only has 30 or so cells of number not a big file.

 

[Adam]

Try creating a column to the right of the dollar values, and using this formula:

=VALUE(D1)

...or something like it. D1, of course, would refer to whatever cell your un-add-able dollar value is in. Then, try adding the results of the VALUE formulas.

If this works, then at least you know that the problem isn't a broken Excel or some weird sheet error, but rather that the dollar value cells themselves are formatted in some odd way. You can then troubleshoot better from there. Also, at least you'll have the sum you need.

 

[PatM]

Yes, or multiply them all by 1.
If you hit Ctrl-1 while on a cell, the 'format cells' popup window will tell you what data type it is.

 

[doug reich]

Data -> Text to Columns will also help.

 

[paulasch]

Try creating a column to the right of the dollar values, and using this formula:

=VALUE(D1)

...or something like it. D1, of course, would refer to whatever cell your un-add-able dollar value is in. Then, try adding the results of the VALUE formulas.

If this works, then at least you know that the problem isn't a broken Excel or some weird sheet error, but rather that the dollar value cells themselves are formatted in some odd way. You can then troubleshoot better from there. Also, at least you'll have the sum you need.

It worked Thanks a bunch any idea how I can figure out why this is happening?

 

[doug reich]

It is happening because excel is importing your columns as text for some reason. This generally happens when the number field being imported has a space in it.