• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

Excel spreadsheet problem

Joined
4/8/09
Messages
2
Points
11
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.
 
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.
 
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.
 
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?
 
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.
 
Back
Top