Forum Discussion
Excel 365 direct typed formulas containing decimals do not calculate and gives a #FIELD! result
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.
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 🙂