1

We have a strange error and sadly it is impossible to search for something like this since so many other non related search results pop up. So I have to give it a try here.

One of my co workers has the strange issue with Excel that when she enters -0.02 or something similar like -0.51 it ends in #Value! Even if we try to change the cell or colume from General to anything else, the preview already stats that it is #VALUE!.

When I check the value it is =0.02 instead of -0.02. The rest of those. When I check the same table in Excel 2010 it shows "=_FV(0;"02")"

When I try to enter -0.02 it says "Micrsoft Excel found an error in the formula entered."

Does anyone have an idea what the issue might be?

Edit: So here is an update. I found a solution for this issue but it seems to only work under Windows 7 and not under Windows 10.

The regional settings would need the following settings to work as the users needs it. Under Numbers: Decimal symbol: , Digit grouping symbol: "Space" List separator: ;

Under Currency: Decimal symbol: , Digit grouping symbol: "Space"

For the rest we had the normal default format settings of French.

So the issue for the user is that either the CSV files are messed up on one particular spot because the last column ends with "1,000000000" or "1.000000000" as a value which should result in the value 1 to appear and not 1.000.000.000 and the other issue is that when this works perfectly fine the next issue would be that the -0.2 values or anything similar to -x.x results in the "#VALUE!" error.

It is a shame that our company policies don't allow for any information to get out otherwise I would put up a copy of the files so you could see for your self.

Edit 04/2020: We found the issue. Since she has moved from Windows 7 and Excel 2010 to Windows 10 and Excel for Office 365 the language and regional settings won't apply properly anymore. The language settings under windows 7 were: Format: English (UK) In Additional settings we set the following: Numbers: Decimal symbol: , Digit grouping symbol: space List seperator: ;

Currency: Decimal symbol: , Digit grouping symbol: space

Time: AM and PM symbol: Empty

Improve this question
8
  • 2
    Check: is locale set to have , instead of . as decimals separator?
    – Hannu
    Jan 3, 2020 at 11:37
  • Also, try using ' before numeric values to express them as strings
    – spikey_richie
    Jan 3, 2020 at 11:56
  • 1
    Need more information. When I try to enter -0.02 it says "Micrsofot Excel found an error in the formula entered." This is usually from dot not being the decimal separator, after rejecting the correction that Excel wants to make. But you said you just got the bad formula message. And what do you mean by When I check the same table in Excel 2010 it shows "=_FV(0;"02")" What same table?
    – Ron Rosenfeld
    Jan 3, 2020 at 12:17
  • @RonRosenfeld I changed the decimal separator and it worked. But with the "=_FV(0;"02")" and same table I mean that I used the same excel file as the user and to them the formula looks like =0.02 and to me it looks like "=_FV(0;"02")".
    – VarmintLP
    Jan 3, 2020 at 12:29
  • @Hannu I think that's the solution. I tried it in my version of the file and noticed immediatly that there was a change and that it works with -0.02. The only question I have why does it wants to turn -0.02 into a formula? Normally it only creates a formula starting with =
    – VarmintLP
    Jan 3, 2020 at 12:33

0

Reset to default

You must log in to answer this question.