Forum Discussion

RFC001's avatar
RFC001
Copper Contributor
Dec 07, 2019
Solved

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...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 09, 2019

    RFC001 

    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.

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Dec 07, 2019

RFC001 

Check your options for decimal separator. It probably is a comma.

 

  • RFC001's avatar
    RFC001
    Copper Contributor
    Dec 09, 2019

    Detlef_Lewin 

    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.

     

Resources