Scalar formula gets same result as array formula

[JenniferMurphy]

I guess I still don't understand array formulas.

Why does the scalar version in D11 get the same result as the array version in D12?

In this case, is there any reason to use one vs the other?

 

[J.Ty.]

Office 365 natively treats formulas as array formulas. This is what happened.

J.Ty.

 

[JenniferMurphy]

Office 365 natively treats formulas as array formulas. This is what happened.

Cool (I think).

Is there any downside to this? That is, are there any expressions that will surprise me and behave differently than they used to?

I found this very interesting article about this and several; new functions:

Excel Formulas Now Spill Into Adjacent Cells - Major Calc Engine Change - Breaking News

Today at the Ignite Conference in Orlando Florida, Microsoft debuted a major change to the calculation engine. Every function is now treated as an array formula and there is no more need to press Ctrl+Shift+Enter. This will lead to a rapid increase in the number of functions introduced in Office...

www.mrexcel.com

Thanks for the tip

 

[J.Ty.]

Array formulas which produce an array of results spill by default in Excel 365. One particular strange downside is the new feature that INDEX(range,x) is an array and spills if x is 0 or close to it. It didn't spill in previous variants of Excel, and instead produced one of the values from range. I know of people whose spreadsheet was working somehow (I would rate this as a design error) with such situations and all of the sudden their formulas started spilling, causing circular references and other unwanted effects.

Below I put an example: test what happens when you change (or delete) the value in C1.

Book1
	A	B	C
1	A	A	1
2	B
3	C
4	D
Sheet1

Cell Formulas
Range	Formula
B1	B1	=INDEX(A1:A4,C1)

 

[JenniferMurphy]

Array formulas which produce an array of results spill by default in Excel 365. One particular strange downside is the new feature that INDEX(range,x) is an array and spills if x is 0 or close to it. It didn't spill in previous variants of Excel, and instead produced one of the values from range. I know of people whose spreadsheet was working somehow (I would rate this as a design error) with such situations and all of the sudden their formulas started spilling, causing circular references and other unwanted effects.

Ok, thanks. I have already gotten the spill error a few times.

 

[MARK858]

Hi Jennifer, if you ever don't want it to spill try putting the @ at the start of the formula (it is backwards compatible).

 

[JenniferMurphy]

Hi Jennifer, if you ever don't want it to spill try putting the @ at the start of the formula (it is backwards compatible).

Yeah, I have been meaning to do some experimenting with that "@" sign. I noticed that Excel 365 added it to a lot of the expressions in the workbooks I imported from Excel 2007. All of these little "enhancements" are why I procrastinated for years upgrading from my beloved XP.

 

[MARK858]

The @ isn't really an enhancement as it was always there but invisible on in the earlier versions (the Ctrl-Shift Enter used to over-ride it).

 

[JenniferMurphy]

The @ isn't really an enhancement as it was always there but invisible on in the earlier versions (the Ctrl-Shift Enter used to over-ride it).

Maybe "enhancement" was the wrong term. I was trying to be charitable.

Thanks for the clarification. I need to do a little more study.