Forum Discussion
Excel 365 direct typed formulas containing decimals do not calculate and gives a #FIELD! result
I can enter +50/2 and the result is 25. But is I enter +50/2.1 the result is #FIELD!
After trying many solutions now the result is now the opposite with a window saying "There's a problem with this formula" (like if I am not trying to enter a formula but a text)
I have updated to the last version
Any help?
That's an interesting case. Up-front "+" without "=" is Lotus 1-2-3 compatibility way to enter formulas and quite useful if to use numpad only. Another example of such compatibility is =SUM(A1.A10).
That worked fine before data rich types were introduced in Excel 365. With it dot has it's own meaning in Excel and #FIELD! error could appear when Excel parse the formula.
I don't know why Excel still parse numeric expression correctly for the cell with General format and gives an error with any other format. However, if switch on this compatibility setting
expressions like +25/2.4 will be parsed correctly
I didn't test on other Excel versions, my guess the issue is only on ones with rich data types.
9 Replies
That's an interesting case. Up-front "+" without "=" is Lotus 1-2-3 compatibility way to enter formulas and quite useful if to use numpad only. Another example of such compatibility is =SUM(A1.A10).
That worked fine before data rich types were introduced in Excel 365. With it dot has it's own meaning in Excel and #FIELD! error could appear when Excel parse the formula.
I don't know why Excel still parse numeric expression correctly for the cell with General format and gives an error with any other format. However, if switch on this compatibility setting
expressions like +25/2.4 will be parsed correctly
I didn't test on other Excel versions, my guess the issue is only on ones with rich data types.
Lotus compatibility?! Awesome finding.
To sum it up:
If you want to start a formula with a minus- or plus-sign and to avoid either #FIELD! error (with decimal separator dot) or formula entry error (with decimal separator comma) you have to tick Transition formula entry.
And it works in Excel Online, no issues.
RFC001, maybe you can upload your excel with an example and we'll try if the same error persists on ours. Alternatively, you can try the calculation on other cells and if it is correct, copy the entire cell to replace the original 'faulty' ones.
Are you by any chance, referencing from other sources? Referencing resulting in such error usually due to different formats. If you are just keying in like normal input then an example excel would be great. Thanks.
crazyshootsI am uploading a sample excel sheet, I hope the NOTES are clear. Try entering direct formulas in the F column cells and see the results. I have indicated what format each one has.
RFC001 Hi, I noticed that only general have the formula (although all were formatted) and when I copy the formula only down, all the results showed perfectly.
Can you help to check on the following 2 methods?
1. Excel options -> Advanced -> Use system operators is ticked.
Decimal operator = . (full stop) & thousand operators = , (comma).
If yes, please check regional settings on your computer.
Go to control panel -> Clock & Region -> Additional Settings -> and change it to what you see on my screenshot. If there are variance, please change to be same as my screenshot and see if this helped 🙂
Thank you Detlef_Lewin
I checked the decimal separator in Options/Advance/Use system separators. No change
But I found that in "General" cell format you can introduce a formula with decimals and the answer is correct (a simple example being +25/4.5 )
But trying to write the same simple formula and this time formatting the cell to "Number", "Currency" or "Accounting", will always result in an error.
So, I can't format the spreadsheet to anything but "General" if I want to introduce formulas with decimals. It was not like this before.
