Hello Kohei, Eike, all,
for public interest, I post my knowledge about formula separators in MS
Excel into the mailing list...
After some testing with different Excel versions with different UI
languages (3.0 German, 4.0 German, 5.0 German, 2003 English and German,
2007 English) in combination with different system locales (German,
English, French, Japanese), I think it works following the rules below.
There are 4 types of variable formula separators/operators in Excel:
1) Function parameter separator
2) Union operator (range list operator)
3) Array column separator
4) Array row separator
1) and 2) are always equal. 3) and 4) are always different. 1) and 2)
are independent from 3) and 4). All separators are dependent on, and may
change with the current system decimal separator.
All other formula operators are fixed and independent from current
system settings. Group separators (a.k.a. thousands separators) are not
allowed in formnulas.
1) 2) The Parameter Separator / Union Operator
==============================================
Example (english): =SUM(0.5,1,(A1,A2))
The parameter separator is taken from the Windows regional settings
("list separator"). By default, Windows uses the comma or the semicolon,
dependent on the locale. This default is independent from the default
decimal separator.
Examples:
System locale Default decimal separator Default list separator
----------------------------------------------------------------------
en_US (USA) Period Comma
de_DE (Germany) Comma Semicolon
de_CH (Switzerland) Period Semicolon
Both separators can be changed manually, e.g. to a # or *, which may
cause any kind of confusion in Excel (e.g. the list separator set to the
asterisk makes it impossible to enter multiplication, tried with Excel
5.0 and 2003). If both separators are equal, Excel changes the list
separator to a semicolon. If both separators are the semicolon, Excel
DOES NOT change the list separator to something else, which leads to
unusable formulas.
Examples:
Legend:
DecSep = current system decimal separator
ListSep = current system list separator
ParamSep = resulting parameter separator / union operator in Excel
DecSep ListSep ParamSep Example formula
--------------------------------------------------------
. , , =SUM(0.5,1,(A1,A2))
. ; ; =SUM(0.5;1;(A1;A2))
. . ; =SUM(0.5;1;(A1;A2)) (*)
. # # =SUM(0.5#1#(A1#A2))
, ; ; =SUM(0,5;1;(A1;A2))
, . . =SUM(0,5.1.(A1.A2))
, , ; =SUM(0,5;1;(A1;A2)) (*)
, # # =SUM(0,5#1#(A1#A2))
; . . =SUM(0;5.1.(A1.A2))
; , , =SUM(0;5,1,(A1,A2))
; ; ; =SUM(0;5;1;(A1;A2)) (**)
; # # =SUM(0;5#1#(A1#A2))
# . . =SUM(0#5.1.(A1.A2))
# , , =SUM(0#5,1,(A1,A2))
# ; ; =SUM(0#5;1;(A1;A2))
# # ; =SUM(0#5;1;(A1;A2)) (*)
(*) parameter separator changed to semicolon
(**) both system separators semicolon, leads to unusable formula
3) 4) The Array Separators
==========================
Example (english): =SUM({1.5,2;3,4})
A) The array column separator is dependent on the Excel UI language,
especially the default decimal separator of this language. Possible are
the period (e.g. English UI) and the comma (e.g. German UI). If it is a
period, the array column separator will be a comma. If it is a comma,
the array column separator will be a period.
B) The array row separator is a semicolon.
C) If the array column separator or the array row separator are equal to
the current system decimal separator, the respective array separator is
replaced by a backslash.
Examples:
Legend:
ExcelUI = current Excel UI language (with internal decimal separator)
DecSep = current system decimal separator
ColSep = array column separator
RowSep = array row separator
ExcelUI DecSep ColSep RowSep Example formula
----------------------------------------------------------------
English (.) . , ; =SUM({1.5,2;3,4})
English (.) , \ ; =SUM({1,5\2;3\4}) (*)
English (.) ; , \ =SUM({1;5,2\3,4}) (*)
English (.) # , ; =SUM({1#5,2;3,4})
German (,) , . ; =SUMME({1,5.2;3.4})
German (,) . \ ; =SUMME({1.5\2;3\4}) (*)
German (,) ; . \ =SUMME({1;5.2\3.4}) (*)
German (,) # . ; =SUMME({1#5.2;3.4})
(*) one array separator replaced by backslash
Conclusion: This is a big mess, and I want to see fixed separators... ;-)
Regards
Daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
