Is there now a change in how array formulas work? (A bit mysterious)

[TomCon]

Testing this formula
=SUM(IF(goal!R1551:R1600=goal!R1551:R1600,1))
As you see it should evaluate TRUE for each of 50 rows (since identical expression on both sides of the = sign), and so result in 50.
It works both entered as an array formula or entered as a regular (non-array) formula.

It seems to me that in the past this had to be an array formula and if you did not enter it as such, it would result in an error.

Now look at this test example.
=SUM(IF(OFFSET(goal_rng,1550,COLUMN(goal!$R$1),50,1)=OFFSET(goal_rng,1550,COLUMN(goal!$R$1),50,1),1))
This expression also has identical expressions on both sides of the = sign and so should evaluate TRUE for every case, and TRUE is set to 1, so the SUM should evaluate to 50.

If you enter it as a non-array formula, it evaluates to 50.
If you enter it as an array formula, it gives #VALUE.

I would have thought it needed to be entered as an array formula.

Can anybody explain/straighten this out for me?

Thank you.

 

[cyrilbrd]

Hi, have you read this?

 

[TomCon]

Hi, have you read this?

Thank you for that link.

Its not that well written an article, though. Is the summary simply "you no longer need old style array formulas, and you now just enter the same old syntax of an array formula with 'regular ENTER' (instead of Ctl-Shift-Enter)...and if the syntax is correct for what an array formula would have been, it will be interpreted in the same way that the old-style array formula would have been interpreted"?

What i just said seems to be correct for my first example [=SUM(IF(goal!R1551:R1600=goal!R1551:R1600,1)) ] as it is interpreted correctly as an old-style array formula, even without Ctl-Shift_Enter. The second expression is a little more ambiguous as it ONLY works if entered "regular" and not as an array formula.

I guess my conclusion is "just enter an array formula now without Ctl-Shift-Enter and see if it works". Does that seem right to you?

Thanks!