Excel Help

So depending on your excel it could be that it doesn't see "." as a decimal point and they need to be moved to a "," instead or it could be that the values are set as text and excel can't sum it, as such they need to be converted to numbers
 
Try changing your decimal seperator to . in your windows settings
 
A lot of the time excel treats imported number fields as text (formatted as text). If you hover over one of the cells you may get icon with an exclamation park, click that and choose convert to number.

If this works you can convert your whole column if the select all the values and hover over.. Once done your sum function should work...
 
For some as yet obscure reason, Microsoft has changed the decimal indicator from being a full-stop & the list separator as being a comma in Windows 7, when the regional settings are set to South Africa, to instead reflect as a comma for the decimal indicator & either a semi-colon (or a comma as well) as the list separator, when using either Windows 8.x or Windows 10.

This throws out calculations when using data compiled in Excel on a Windows 7 PC using a full-stop, and using that same spreadsheet in Excel on a Windows 8.x or Windows 10 PC, where it expects a comma as the decimal separator.

It may also affect CSV data import as well, as Excel in Windows 7 used a comma, whereas Excel in Windows 8.x or Windows 10 may be expecting a semi-colon as the CSV list separator.

Here's how you solve this issue - you may have to do this on ALL PC's in your environment that are on Windows 10, so that the correct symbols (full-stop for decimal, comma for list separator) are recognised.

First make sure that any MS Office programs - Excel, Word, Outlook, etc are all closed as this change will affect their global settings, once you have followed the procedure below.

  1. Go to Settings in Windows 10 (I am using version 1903 for these steps) - maximise the Settings Window so you can see all the menus that follow on both the left & right screen panels
  2. Click on Time & Language
  3. At the top right - under Related Settings, click on Date, time & regional formatting
  4. When the Region screen appears, again go to Related Settings at the top right, and click on Additional Date, time & regional settings
  5. A new Control Panel pop-up window will appear for Clock & Region settings
  6. Under the second option - Region - click on Change date, time or number formats
  7. Yet another Control Panel pop-up window for Region will appear
  8. Make sure Format at the top is set to English (South Africa)
    Then click on the Additional Settings button at the bottom of the Region window
    A new pop-up window for Customise format will appear
  9. The two options you need to change are as follows:
    Delete the comma symbol in the Decimal symbol option and type in a full-stop
  10. Make sure that the entry in the List separator option is a comma and NOT a semi-colon (change if necessary)
    Click the Apply button and then the OK button - the Customise format pop-up will close
    Click the OK button of the Region pop-up window - it will also close
  11. Close the Control Panel window for Clock & Region
    Close the main Windows 10 Settings window
You can re-open Excel and from now on it will now recognise the full-stop as the decimal & the comma as the CSV list separator.
 
Ivan Leon said:
For some as yet obscure reason, Microsoft has changed the decimal indicator from being a full-stop & the list separator as being a comma in Windows 7, when the regional settings are set to South Africa, to instead reflect as a comma for the decimal indicator & either a semi-colon (or a comma as well) as the list separator, when using either Windows 8.x or Windows 10.

This throws out calculations when using data compiled in Excel on a Windows 7 PC using a full-stop, and using that same spreadsheet in Excel on a Windows 8.x or Windows 10 PC, where it expects a comma as the decimal separator.

It may also affect CSV data import as well, as Excel in Windows 7 used a comma, whereas Excel in Windows 8.x or Windows 10 may be expecting a semi-colon as the CSV list separator.

Here's how you solve this issue - you may have to do this on ALL PC's in your environment that are on Windows 10, so that the correct symbols (full-stop for decimal, comma for list separator) are recognised.

First make sure that any MS Office programs - Excel, Word, Outlook, etc are all closed as this change will affect their global settings, once you have followed the procedure below.

  1. Go to Settings in Windows 10 (I am using version 1903 for these steps) - maximise the Settings Window so you can see all the menus that follow on both the left & right screen panels
  2. Click on Time & Language
  3. At the top right - under Related Settings, click on Date, time & regional formatting
  4. When the Region screen appears, again go to Related Settings at the top right, and click on Additional Date, time & regional settings
  5. A new Control Panel pop-up window will appear for Clock & Region settings
  6. Under the second option - Region - click on Change date, time or number formats
  7. Yet another Control Panel pop-up window for Region will appear
  8. Make sure Format at the top is set to English (South Africa)
    Then click on the Additional Settings button at the bottom of the Region window
    A new pop-up window for Customise format will appear
  9. The two options you need to change are as follows:
    Delete the comma symbol in the Decimal symbol option and type in a full-stop
  10. Make sure that the entry in the List separator option is a comma and NOT a semi-colon (change if necessary)
    Click the Apply button and then the OK button - the Customize format pop-up will close
    Click the OK button of the Region pop-up window - it will also close
  11. Close the Control Panel window for Clock & Region
    Close the main Windows 10 Settings window
You can re-open Excel and from now on it will now recognise the full-stop as the decimal & the comma as the CSV list separator.
Click to expand...
This worked, thanks then I converted the "text - number" in error on formula working perfect

Thanks'
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X