error in ADRES() function using an array constant parameter

[tomsuters]

consider the following ADRES matrix formula with an array constant as the column parameter:

={ADRES(33;{1,2;3,4})

It gives the following 2x2 matrix as result:

$A$33 $A$33
$C$33 $C$33

It seems only the column part gets computed, not the row part. What is wrong?

[#2]

@tomsuters That's odd. This is what I get.

[#3 · Accepted solution]

@tomsuters 

This is most likely due to the fact that your system locale uses the comma as decimal separator. As such, the array constant

{1,2;3,4}

does not represent a 2-row-by-2-column array; rather a 2-row-by-1-column array, the 2 elements of which are one and two-tenths and three and four-tenths.

The ADDRESS function's row_num and column_num parameters accept non-integer inputs, truncating to the integer portion only in such cases.

Also, I assume you must be selecting four cells within the worksheet and array-entering that formula with CSE. Any cell within the first row of your selected array will return the result of

ADDRESS(33,1)

And any cell within the second row of your selected array will return the result of

ADDRESS(33,3)

You need to find out what represents the separator for horizontal arrays for your version of Excel; clearly it is not the comma. For example, assuming it is the backslash (\), the correct syntax would be

=ADDRESS(33;{1\2;3\4})

Regards 

[#4]

@Riny_van_Eekelen The suggestion of @JosWoolley (see below) was correct.

[#5]

@JosWoolley thx very much you are correct! Now I have three options to let Excel detect the separator in an array constant:

1. change the decimal sign in Excel options to something else than comma.  Downside: all my excel files now behave different from all other Windows applications wrt number notation.
2. change the Windows locale settings for decimal sign to something else than comma which makes all applications behave different from what I am used to wrt number notation.
3. set the separator for array constants to a different sign in Excel. This would be my preferred option but I don't know where or how to do that ni Excel.

comment to MSFT: silently rounding fractions to whole numbers without warning or error seems not only very odd but also leaves you completely in the dark what goes wrong! Are there more Excel functions where fractions get silently rounded off?

[#6]

Not sure I understand. What is wrong with you keeping the array constant separator as it currently is? By that I mean that, since your version uses the comma as decimal separator, I would be amazed if it also uses the comma as horizontal separator in array constants. As such, what precisely is your issue here? I understand that this means that a lot of formulas posted on Excel sites/blogs/forums will not work for you unless first translated correctly, but this is nothing new.

Regards

[#7]

@JosWoolley 

Hi Jos,

in fact my Excel version allowed me (to your surprise?) to use the comma as my Windows decimal sign and ALSO as horizontal separator in array constants leading to the erroneous result you described.

Option 1 and 2 solved this by keeping the horizontal separator ni Excel as is and change the decimal sign in either Windows or Excel. They both work fine but I found the consequence "inconvenient" as the number representation changes in Excel respectively all Windows applications (without having to change the applications themself).

So the obvious third option is to try to use a different horizontal separator sign in Excel but to my knowledge Excel does not support such an option or says which other sign to use in case the comma clashes with the decimal sign. Hence my question if such an option or fall-back sign for array consatants exist. In the mean time I learnt that the backslash sign '\' forfills that purpose alhough this is nowhere documented or officially supported by Excel I believe.

In fact I found that if you switch the Windows locale from comma to dot, Excel automatically replaces the backslash by a comma in the array contants of the formula and vv!! 

So for me this  problem now seems to be solved with t5he backslash and thx again for your input.  Below I try to summarize what Excel computes for the 4 different cases:

I already want to attract your attention to my next problem with array constants in ADRES() in combination with INDIRECT() but I will start a new topic for that. Hope you can help out there too!

Tom