Formula won't calculate - decimal settings?

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,300
Office Version
  1. 365
Platform
  1. Windows
I bumped into something weird: some formulas won't auto-calculate and it seems to me that it has to do with the decimal symbol. My model both runs on systems that have a . as the decimal symbol and the , as a decimal symbol (let's call them dot and comma). So I created some functions to format text, but have to do add some catches to make that work: =TEXT(1.234,"0.00") will show "001" with a comma-system and 1.23 with a dot-system. So I created a cell to determine the decimal symbol: =MID(""&1/10,2,1). That works like a charm and gives me a dot or a comma which I can than use in TEXT formulas.

However: sometimes when my co-worker with a dot setting has worked on the file and I (with a comma setting) pick it up, that cell will show a dot when I open the file (being: his decimal symbol) as an outcome of that formula. That cell/formula simply does not recalculate... I tried: recalculate workbook, recalculate sheet, even through VBA Range("D21").recalculate, but that doesn't do anything. If I copy the cell and paste the formula in another cell, it shows the correct outcome in that new cell, but the old one still shows the dot as it hasn't recalculated. What does work: select the cell contents and press enter or in VBA Range("D21").FormulaR1C1 =Range("D21").FormulaR1C1
Similarly, there are some cells with a simple TEXT formula that also won't recalculate, I have one cell like the example (=TEXT(1.234,"0.00") ) and it will show me 1.23 when I open the file and only change to 001 once I click the cell (or do the FormulaR1C1 VBA trick).

Does this sound familiar to anyone? I can force the recalculation through VBA but would like to know why this happens?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,664
Office Version
  1. 2010
Platform
  1. Windows
you could try wrapping the calculation in an if statement which includes a volatile function which will force a recalculation such as:

VBA Code:
=IF(FALSE,TODAY(),MID(""&1/10,2,1))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,251
Messages
5,600,540
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top