Forum Discussion

MarkMarkwj's avatar
MarkMarkwj
Copper Contributor
May 18, 2020

Excel changing numbers, removes the decimal point.

I have a little spreadsheet I use to calculate gas mileage, costs, per tank and annually etc. Excel has decided to eliminate the decimal point in the number field for gas, so I type 12.215 and excel ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 22, 2020

JMB17 

IMHO, the setting changes the behaviour for entire file. I doubt if it affects behaviour when values in one range ignores separator and in another one - not. Both have the same format applied.

JMB17's avatar
JMB17
Bronze Contributor
May 22, 2020
Yes, I know it's an application level setting and affects the entire file. Please note that I said it was probably changed, then the additional two numbers keyed, and then changed back.

I was able to replicate the data he is seeing (correct and incorrect numbers), your opinion notwithstanding, so I do think it is a possibility and the OP should double check the application and machine settings to confirm which one it is using and whether or not they are set correctly.
  • MarkMarkwj's avatar
    MarkMarkwj
    Copper Contributor
    May 26, 2020

    JMB17 Thanks all I had tried this previously, doesn't seem to work for me. I also just tried to make a brand new spreadsheet and the same error appears??? closed and reopened the spreadsheet and made the suggested changes, retyped the decimal point all to no avail still have the same issue.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 27, 2020

      MarkMarkwj 

      Mark, how exactly "same issue" looks?

      - you open new workbook

      - type numbers like 12.345 into the cells from top to down

      - in rows from 1 to 12 (or so) Excel shows as it is entered, i.e. 12.345

      - in next rows starting from #13 Excel shows 12345 ignoring decimal point

       

      Or that's another behaviour?

      • MarkMarkwj's avatar
        MarkMarkwj
        Copper Contributor
        May 30, 2020

        SergeiBaklan Sergei, et al, yes you describe the issue correctly above.

         

        I tried making a new worksheet. Typed in 12.212 as an example of the data I want to enter. Excel shows it as 12212. No decimal.

         

        I went to the Control Panel and checked the region/language, re-entered these as USA and English. I also when to File-Options-Advanced-Editing Options and re-entered the separators as a "period" for the decimal separator and a "comma" for thousands separator. Spreadsheet still only will show 12212. 

         

        Attached is today's test. Interestingly under the second column (Miles) Excel takes my enter of 250 and turns it into 0.25????

        As it stands Excel is useless for such a simple set of calculations. 

    • JMB17's avatar
      JMB17
      Bronze Contributor
      May 26, 2020

      MarkMarkwj 

       

      You also checked Windows number formats under region/language settings in control panel? 

      With both the decimal separator and group separator set to a period (instead of group separator being a comma), I get exactly what you are seeing (new inputs have the decimal moved 3 places without affecting the existing numbers).

       

      I downloaded your file, keyed another entry, and get exactly what you are seeing (keyed as 12.215 and 1.719 and the decimal moved 3 places after hitting enter).

       

      If this is not your issue, I'm afraid I'm at a loss for any further suggestions.

       

       

Resources