Feb 01 2023 03:55 PM - edited Feb 01 2023 03:58 PM
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?
Feb 01 2023 07:44 PM
@tomsuters That's odd. This is what I get.
Feb 01 2023 09:57 PM - edited Feb 01 2023 10:08 PM
SolutionThis 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
Feb 02 2023 07:12 AM
@Riny_van_Eekelen The suggestion of @JosWoolley (see below) was correct.
Feb 02 2023 07:56 AM - edited Feb 02 2023 09:00 AM
@JosWoolley thx very much you are correct! Now I have three options to let Excel detect the separator in an array constant:
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?
Feb 02 2023 09:38 AM - edited Feb 02 2023 09:39 AM
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
Feb 02 2023 07:49 PM
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