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.
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.
Detlef, I'm surprised as well.
One more condition to sum up - to the cell it shall be applied any format except General, with the latest it works.
