LinkBack URL
About LinkBacks
My new computer at work was recently installed with Excel 2010. The formulas don't seem to work on my computer, but the exact same formulas work just fine on my colleague's computers. Please help
My new computer at work was recently installed with Excel 2010. The formulas don't seem to work on my computer, but the exact same formulas work just fine on my colleague's computers. Please help
How exactly are the formulas not working? Are the cells formatted as text?
Spread the love, add to the Rep
"None of us are as smart as all of us."
The data is copied in from another source (a GPS controller) and the cells are are formatted as general. The equations work when I physically type in the data one by one in each cell, but I am working with large amounts of data.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
All formulas are not working ? or only a specific formula not working ?Originally Posted by ronnie_olivier
☚ Click ★ just below left if it helps, Boo?ath?
All the formulas are not working with the data copied in from a different source. The formulas work when physically typing the data in
That sounds like the data are somehow being imported in an an incorrect format. Numbers stored as text is one possibility that has already been mentioned. Or numbers with incorrect decimal and/or thousands separators, preventing them from being recognized as numbers. Or numbers with extra spaces or other non-printing character that prevents Excel from seeing them as numbers.
Debugging steps I would start with
Use ISTEXT() and/or ISNUMBER() to see if the data are being recognized as text or numbers
Use the TRIM() function to remove unwanted spaces/tabs/nonprinting characters.
See where that gets you.
I almost discovered the problem. Some of the data (coordinates) that I copied in had spaces in between the values (ie 3 050 236.365). When I took the spaces out and replaced the comma with a full stop then the equations worked.
But now my next question is how to avoid this problem by automatically copying the data from another source (ie a text file) into excel without the values having spaces between them?
Details will probably depend on the specific layout of your data. Is the space being used here as a "thousands separator"? Is space also used to delimit the data, or is some other delimiter being used?
If this is the only use of the space character in the file, I would probably perform a simple find and replace command (in the text editor before importing into Excel, though it can also be done in excel). Find the space and replace with nothing or with a comma (or whatever your system is using for a thousands separator). That will put the data into a format that Excel can recognize.
If space is being used both as a thousands separator and a delimiter, then you will need to examine your data and see if there is some other identifying character (or maybe the data can be called fixed width) that can be used to eliminate or change the space characters being used as thousands separators.
Where are the data coming from? Perhaps there is something that can be done at the source of the data to make a better choice for thousands separator.
Thank you that is very helpful. The data is coming from a GPS controller with co-ordinates on it, and also imported from a survey programme called SURPAC, which outputs the coordinates into a text file.
With that in mind, I would probably start in SURPAC and see what options and controls I had available to me in SURPAC to control the formatting and spacing and delimiting in the output text file. Adjust the settings in SURPAC to make it easier to import into Excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks