Are French and English Excel formulas compatible?

[Guest]

I have an Excel file with formulas in it. If I change the formula in the
French version of Excel 2000 and I try to open the file with the English
version, instead of the result of the formula, I get the #VALUE! in the cell.
What can I do if I want users to see the result no matter what the version is
(French or English)?

 

[Frank Kabel]

Hi
you're probably using formulas from the Analysis Toolpak Addin. You may
post which formula raised the error as for many ATP functions
workarounds are available

 

[Guest]

The formula is very simple. In French, it's =SI(E24=0,0,E23/E24). If I open
the Excel file in the English version, I can see the formula
=IF(E24=0,0,E23/E24) but the cell shows #VALUE! instead of the calculation...

Thank you in advance.

 

[Frank Kabel]

Hi
does hitting F9 help in this case?. Normally it should work without
this problem

 

[Guest]

No, it doesn't work... Any other ideas?

Hi
does hitting F9 help in this case?. Normally it should work without
this problem

 

[Dave Peterson]

Maybe the formula is working perfectly, but you don't have valid numbers in both
E24 and E23.

If you do this:

=count(e23:e24)
(translated, though!)

Do you get 2?

 

[Zbigniew Malinowski]

Hi,

I suppose there's not only Excel language version problem but also a locale
Settings difference. Could you check it, please?

This is my example: the same formula in Polish should be:

=JE¯ELI(E24=0;0;E23/E24) - a period is decimal sign in Poland. I think this
is also true in France (n'est pas vraie?). But in some regional settings
(list separator and decimal sign) I should be able to use a period instead
of a comma for parameters separation. In this case a translation for
English version could not be obvious.

So I'm pretty sure that the formula in 'normal' French Excel should rather
be: =SI(E24=0;0;E23/24) I'm really surprized that the one with periods work

So, in this complicated translation situation I think Excel don't know what
to do. Maybe theres something like:

0,0,E23/E24 means 0,E23/E24 means something like non decimal format (ex. 2/3
instead of 0,667) and the formula become invalid?

I'm maybe wrong but could you confirm if I really am? I'm working in
Polish/French/English environment and the problem is very interesting for
me.

Zbig

 

[Myrna Larson]

I'm not sure what your question is. When entering a formula, Excel requires
that you use the list separator and decimal separator characters as they are
defined in your Windows' Regional/International Settings.

 

[Zbigniew Malinowski]

I'm afraid you're wrong. In my Windows XP (US version with Polish MUI) with
Polish version of Excel 2000 the system list separator regional settings has
no influence on Excel. My Excel wants a comma ( ; ) for parameter
separation even if a period ( , ) is defined as list separator. However a
system decimal regional settings has influence on Excel since I'm able to
define a point ( . ) instead of period ( , ) and to use it in Excel.

There are many similar examples: Polish Excel is writing *.CSV files using
period ( , ) as separator but wants a comma ( ; ) when reading! Impossible
to change its behavior...

My supposition was that there are many factors that influence formulas
translation and maybe in this particular situation Excel is lost... It's
just a supposition based on French formula mentionned which is incorrect
from my point of view.

Zbig

 

[Myrna Larson]

Now I'm really confused. ";" is not a comma, it's a semicolon. "," is not a
period, it's a comma. Maybe that's part of your problems.

AFAIK, if you type a formula in English Excel, with the appropriate list and
decimal separators, then open that file in a different language version, the
list and decimal separators that you see on the screen will change to match
the settings for that new machine.

OTOH, if your problem isn't with worksheet formulas but instead relates to
reading and writing CSV files, AIR, they are always written with a comma
between fields -- that's what the name says -- COMMA SEPARATED VALUES, and
that's what it means; "," separates the fields.

 

[Dave Peterson]

I think the CSV file picks up the list separator--unless you're using VBA.

When you save a file in code as xlcsv, it uses the comma (,).

But when I toggled my list separator in regional settings, and saved a workbook
through File|SaveAs, the text file delimiter matched the regional settings
character.

 

[Myrna Larson]

Just to clarify, you saved as text, not CSV, right?

 

[Myrna Larson]

I stand corrected. I changed my list separator, decimal separator, and digit
grouping characters, and these settings were preserved when I saved the file
as CSV or TXT, from Excel proper. I didn't try it from VBA.

 

[Dave Peterson]

No, comma delimited. (*.csv)

I bet you were confused about this line:

But when I toggled my list separator in regional settings, and saved a workbook
through File|SaveAs, the text file delimiter matched the regional settings
character.

I meant that when I opened the .csv file in my favorite text editor, the
delimiter matched the regional settings choice.

 

[Myrna Larson]

No, I wasn't clear on whether saving to TXT (tab-delimited) and to CSV behaved
the same way WRT to the other settings -- decimal, number grouping, etc.

The field separator for TXT should always be the tab character, AIR.

 

[Frank Kabel]

Hi Myrna
Dave is correct. If I save a file as *.csv file via Excel's Saveas
dialog in my German Excel/Windows it uses the semicolon ( as this is
the list separator defined in the regional settings

this is one of the main pains then dealing with *.csv files in a
multi-language environment )

 

[Myrna Larson]

Yes. That's what I found, too.

The difference comes when saving as CSV from VBA code: there, with the list
separator set as ";" in Regional settings, the file is written with comma
separators.

 

[keepITcool]

Note on saving and opening CSV/TXT files in international environments.

It's NOT just the field separator you need to worry about, Number and
data formatting is also affected.

IF you have xlXP or xl2003 the SaveAs and OpenText methods include a
new argument called "Local".

(which is NOT picked up by the macrorecorder, not listed under what's
new AND the help file does a very limited job of describing it's far
reaching effect...


When using the UI the local argument is TRUE and Regional settings are
used for separators, number and data formatting.

When using VBA the "local" argument defaults to FALSE, and USenglish
settings are used to Write resp Read the file.

HTH...