Excel not recognising 1 or decimals as numbers

[joeymagic]

Hi

I have a weird problem with Excel. It recognises all numbers as numbers but excluding the number 1. It is only recognised as text as well as a decimals, for example 3.4. So every time I try and add these values up it completely ignores 1 and decimals.

Have I modified a setting?

Any help would be much appreciated.

[daddylonglegs]

If some of your cells are text-formatted then any "numbers" they contain will be ignored if you use a SUM function. Are the 1s and decimals generated by formulas?

[joeymagic]

The numbers are not being generated via formulas and the cell format is set to number. I wonder if the preferences for excel (if there is a file for them) has become either corrupted or another user on the machine has touched some settings I can't seem to find.

[Leith Ross]

Hello joeymagic,

Does this happen only on one worksheet? Does this happen when you open known good workbooks as well?

Sincerely,
Leith Ross

[jbricher]

Have you tried typing an equals sign in the function bar before the number, this may help distinguish it as a number to the system, despite your formatting selection.

[daddylonglegs]

If cells are text-formatted then changing the format to number doesn't change anything. Try this

Select column of numbers > Data > Text to columns > Finish

Does the sum work now? If not then what formula are you using to sum the values?

[joeymagic]

Thank you all for your replies!

Unfortunately it does happen to the whole workbook and not just a worksheet, I also tried jbricher's method of adding a equals sign and that worked one but not for a decimal number.

As for changing the text to column value it still did not resolve the issue of adding 1 + 2.3 + 3.5, =SUM(1+2.3+3.5). Its the most bizzare problem I have encountered with excel!

[daddylonglegs]

Can you post the exact formula you are using, what the result should be and the result you get? It's difficult to profer any advice with only half the information.....

[joeymagic]

The exact formula is =SUM(A2:A5) the result being 0
(=SUM(1+2.2+3.5+4.4))
The result should be 11.1

[daddylonglegs]

What do you get if you use either

=A2+A3+A4+A5

or

=SUMPRODUCT(A2:A5+0)

If either or both of these work then it would suggest that your numbers are text-formatted.

[joeymagic]

I have found out that Excel on this machine I'm using is separating decimals with 1 and not either \ or . I found this out by looking at the code. Is there anyway to get the mso-displayed-decimal-separator:1: back to mso-displayed-decimal-separator:"\."