Named ranges with certain formulas don't return correct result

[Tie CHENG]

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?

[#2]

@Tie CHENG 

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.

[#3]

@Tie CHENG 

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?

[#4]

@Sergei Baklan 

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.

[#5]

@Peter Bartholomew 

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.

[#6]

@Peter Bartholomew 

One more. If modify your formula as

result = IF( preTax? + {0}, amount, amount * (1 + rate))

it returns spill.

Same way as =IF( 1, SEQUENCE(5) ) returns single value, but =IF( {1}, SEQUENCE(5) ) - spill.

[#7]

+ {0}
was a bit of a surprise.

[#8]

@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.