Forum Discussion
error in ADRES() function using an array constant parameter
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$33It seems only the column part gets computed, not the row part. What is wrong?
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
6 Replies
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
JosWoolley thx very much you are correct! Now I have three options to let Excel detect the separator in an array constant:
- 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.
- 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.
- 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?
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
tomsuters That's odd. This is what I get.
Riny_van_Eekelen The suggestion of JosWoolley (see below) was correct.
