TEXT function in different localities UK to Poland.

[Darren Bartrup]

I have a workbook that is shared amongst colleagues in different countries on MS Teams.
When the workbook is opened in Poland the cell shows #ARG! error, while it shows #VALUE! error in the UK. Evaluating the formula results in an error, but if I edit the formula and press enter it shows ok, and evaluating works.

An example of the formula is
=TEXT($G51,"#,##0;-#,##0;0;@") & " / " & TEXT(XLOOKUP($H$3,PARawData[Date],PARawData[Inb%]),"0.00%")

G51 contains the value 11820 formatted as #,##0;-#,##0;0;@
The XLOOKUP references a cell containing the value 1 formatted as Percentage.

Does the [$-en-GB] coding only work on dates or can I add it to the start of any format? Is that even the problem?

 

[KRice]

Is the same version of Excel (i.e., 365) being used in both countries?

 

[Darren Bartrup]

Yes, we're all on 365.

Another strange thing happened, from France this time. The value 8.72 when formatted as percent should show as 872%.
The "0.00%" in the TEXT format in my formula changed it to show 8.72%. Once I recalculated it on the UK version it changed back to 872%.

So it calculated incorrectly in France and not at all in Poland. I'm confused.

 

[jasonb75]

"0.00%" is probably not a valid format in countries that use a comma decimal separator.

Likewise, "#,##0;-#,##0;0;@" may also be invalid due to the commas being interpreted as decimals instead of thousands separators. Also in an array constant, it would be necessary to change semi-colons to backslashes when going from a dot / period decimal environment to comma decimal, I haven't done a test to confirm if that applies to formatting but it is a possibility.

As far as I'm aware the use of country codes in formatting is only relevant to dates (dmy or mdy order and spelling of weekdays and month names) and perhaps currency / accounting format symbols.

Probably the easiest fix would be to test a simple decimal with an error trap, then choose the correct formula for the text format based on the result. I believe that (1.1+1) would cause an error in a comma environment.

 

[Darren Bartrup]

Thanks Jason, I'll give that a try.