08-30-2020 06:31 PM
08-30-2020 06:31 PM
I am having difficulty understanding the content of the following webpage:
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?
08-30-2020 07:36 PM - edited 08-30-2020 07:50 PM
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)
08-31-2020 05:16 AMSolution
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.
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
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:
And if formula returns an array, not range, implicit intersection operator returns first element of such array.