Forum Discussion
Named ranges with certain formulas don't return correct result
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?
7 Replies
- JoeMcDaid
Microsoft
PeterBartholomew1 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.
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.
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.
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.
