SOLVED

Implicit intersection operator

%3CLINGO-SUB%20id%3D%22lingo-sub-1621925%22%20slang%3D%22en-US%22%3EImplicit%20intersection%20operator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621925%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20difficulty%20understanding%20the%20content%20of%20the%20following%20webpage%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fimplicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34%23%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fimplicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34%23%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20small%20dummy%20spreadsheet%20that%20uses%20the%20RandomArray%20and%20Offset%20functions.%20I%20am%20wanting%20to%20understand%20when%20I%20use%20the%20%22%40%22%20operator.%20Can%20someone%2C%20please%2C%20open%20my%20small%20spreadsheet%2C%20look%20at%20the%20contents%2C%20and%2C%20perhaps%2C%20offer%20a%20few%20words%20of%20advice%20as%20to%20when%20the%20%22%40%22%20operator%20should%20be%20used%20or%20is%20required%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1621925%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1621974%22%20slang%3D%22en-US%22%3ERe%3A%20Implicit%20intersection%20operator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F549380%22%20target%3D%22_blank%22%3E%40KStecyk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20explanation%20%3A%3CSPAN%3EImplicit%20intersection%20logic%20reduces%20many%20values%20to%20a%20single%20value.%26nbsp%3BExcel%20did%20this%20to%20force%20a%20formula%20to%20return%20a%20single%20value%2C%20since%20a%20cell%20could%20only%20contain%20a%20single%20value.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20new%20versions%20of%20Excel%20some%20formulas%20auto%20fills%20the%20values%20for%20us.%20But%20there%20are%20cases%20we%20don't%20need%20the%20whole%20set%20of%20values%20%22guessed%22%20by%20excel.%20You%20can%20try%20creating%20a%20list%20of%20values%2C%20Month%20Names%20Jan-Dec%20(A1%3AA12).%20Now%26nbsp%3Bon%20C1%20you%20type%20the%20formula%20A1%3AA12.%20Excel%20will%20fill%20up%20all%20the%20rows%20down%20C1%20with%20the%20values%20of%20your%20interval.%20But%20by%20adding%20%22%40%22%20you%20will%20get%20only%20the%20first%20value.%20%3D%40A1%3AA12%3C%2FP%3E%3CP%3EThe%20same%20result%20you%20can%20replicate%20with%20some%20formula%20like%26nbsp%3B%3DCHOOSE(1%2CA1%3AA12)%20and%26nbsp%3B%3D%40CHOOSE(1%3BA1%3AA12)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoesn't%20matter%20if%20is%20a%20Range%20or%20an%20array%20or%20a%20single%20value%2C%20using%20the%20%22%40%22%20operator%20you%20will%20get%20as%20a%20result%20a%20single%20item%20or%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I am having difficulty understanding the content of the following webpage:

https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1...

 

I have created a small dummy spreadsheet that uses the RandomArray and Offset functions. I am wanting to understand when I use the "@" operator. Can someone, please, open my small spreadsheet, look at the contents, and, perhaps, offer a few words of advice as to when the "@" operator should be used or is required? 

 

Thank you!

5 Replies

@KStecyk 

The explanation :Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value.

 

The new versions of Excel some formulas auto fills the values for us. But there are cases we don't need the whole set of values "guessed" by excel. You can try creating a list of values, Month Names Jan-Dec (A1:A12). Now on C1 you type the formula A1:A12. Excel will fill up all the rows down C1 with the values of your interval. But by adding "@" you will get only the first value. =@A1:A12

The same result you can replicate with some formula like =CHOOSE(1,A1:A12) and =@CHOOSE(1,A1:A12).

=OFFSET(A1,0,0,12,1) (Returns all values)

=@OFFSET(A1,0,0,12,1) (Returns only the first value)

Doesn't matter if is a Range or an array or a single value, using the "@" operator you will get as a result a single item or value.

By the way you can test your formulas =@OFFSET(G16:G21,0,0,6,1)

Best Response confirmed by KStecyk (Contributor)
Solution

@KStecyk 

Actually it works as explained in support article and forces exactly the same implicit intersection behaviour as it is in pre-DA Excel where it is performed silently by default.

image.png

OFFSET returns range, not array. Thus second point is applicable to your situation. Since formula returns horizontal range, you may return intersection with the column, not with the row. As for example

image.png

Implicit intersection with the row returns an error since returned by formula range has multiple values in the row, actually we have no intersection here. That could be illustrated on more simple example:

image.png

And if formula returns an array, not range, implicit intersection operator returns first element of such array.

@Juliano-Petrukio 

Thank you for your response. I created a spreadsheet and worked through your examples, which I found helpful.

@Sergei Baklan 

 

Thank you for your response, including your spreadsheet. I opened your spreadsheet and followed your examples. I appreciate that you added additional information to help clarify.

 

 

@KStecyk , you are welcome, glad to help