Jan 20 2022 10:22 AM - edited Jan 20 2022 10:23 AM
I realized that named ranges defined by certain formulas don't return correct value.
For instance, `=IF(ROW(Sheet1!$A$3:$B$3)>0,INDEX(Sheet1!$B$3:$B$8,{3,4}))` in a cell well returns an array `{3,4}` in my example. I define a named range `f` with the same formula, `=f` in a cell returns a single value `3`.
In general, named ranges works fine with array formula. But there is still problem like this. I know that my problematic formula could be simplified (to `INDEX(Sheet1!$B$3:$B$8,{3,4})`), but I would like to know in general what prevents named ranges from returning correct results.
Could anyone advise?
Jan 20 2022 12:37 PM
Naming the formula you actually use it as array formula, in Pre-DA Excel notation like formula to which CSE is applied.
In DA Excel CSE applied to array returns first element of the array.
You may enter your formula with Ctrl+Shift+Enter, i.e.
{=IF(ROW(Sheet1!$A$3:$B$3)>0,INDEX(Sheet1!$B$3:$B$8,{3,4})) }
it returns single value 3.
Jan 25 2022 12:49 PM
It does seem odd. I reduced the formula to the following, which gave a truncated array for '=f'
f:
=IF(TRUE,list1)
g:
=IF({TRUE},list1)
Then again 'g' spilt the whole list.
@JoeMcDaid Is this how you intended it?
Jan 26 2022 03:47 AM
So do you believe this behaviour is just an odd leftover from pre dynamic array days or is it an intentional feature intended to provide backward compatibility? In the attached file I have produced a similar example of an array that is truncated when output to the worksheet, yet will sum correctly in memory. Very reminiscent of pre-DA days.
Oddly though, despite the fact that I was one of very few users that used named formulae on a regular basis to evade implicit intersection, I had not come across this particular issue. Maybe it was that the logical part of the formula was most often itself an array.
Jan 26 2022 02:52 PM
Interesting finding. I don't know what is behind. That's not behavior for any named formula, perhaps with conditional functions only.
If use any of
=IF( 1, result )
=LET( v, result, v)
=+result
they all return spill. If now name above formulae, =newname returns spill for latest two, but single value for first one.
Jan 26 2022 03:01 PM
One more. If modify your formula as
Jan 27 2022 12:50 PM
Mar 01 2022 02:12 PM
@Peter Bartholomew Thanks for looping me in here. Engineering have completed an investigation, and this is a long-standing bug. It's difficult to see any scenarios where the current behavior would be preferred as it's effectively truncating the users array, so we plan to take up a fix.
Being a calc fix, we'll roll this out very slowly to ensure there are no unforeseen issues.