none
Single User/PC having issues with Excel 2016 @ Implicit Intersection RRS feed

  • Question

  • Hi,

    Just trying to understand the changes to Excel that have given rise to the change in display of "Implicit Intersection" (single or @) in Excel formulae. 

       I've one user(so far), who when opening spreadsheets with formula like =VLOOKUP(A:A,'Friday am'!A:G,7,0), now see's the following :-

    =VLOOKUP(@A:A,'Friday am'!A:G,7,0)

    It's apparently "Implicit Intersection" in action, and before today, I'd never heard of it! I previously spent 2 days on and off trying to reset/reinstall Office to get rid of this behaviour, so to find out it's by design & only effects one user still leaves me perplexed!

    • As anyone else had this issue and
    • does it relate to a specific version of excel/update?
    • is there a setting to revert it to it's previous "silent" operation i.e. no @?
    • Is it likely that this "behaviour" spreads as more computers get the office update that causes it?

    Thanks for Any help!

    Dave.

    Monday, December 2, 2019 3:21 PM

All replies

  • Hi Dave,

    The implicit intersection operator is the at symbol (@). In the latest version of Excel for Office 365, Excel for Office 365 for Mac and Excel for the web, which supports dynamic arrays, you'll see the @ symbol more often in formulas, sometimes added by Excel automatically when opening worksheets from prior versions.

    This is done to ensure that older formulas continue to return the same (single) result when they might otherwise spill multiple values onto the worksheet.  In general, functions that return multi-cell ranges or arrays will be automatically prefixed with @ if they were created in an earlier version. This behavior only occurs in dynamic array versions of Excel.

    Here is some information from the official document on explaining Implicit intersection operator: @. I think it would be helpful to you.

    The implicit intersection operator was introduced as part of substantial upgrade to Excel's formula language to support dynamic arrays. Dynamic arrays bring significant new calculation ability and functionality to Excel.

    Excel's upgraded formula language is almost identical to the old language, except that it uses the @ operator to indicate where implicit intersection could occur, whereas the old language did this silently. As a result, you may notice @'s appear in some formulas when opened in dynamic array Excel. It's important to note that your formulas will continue to calculate the same way they always have.  

    With the advent of dynamic arrays, Excel is no longer limited to returning single values from formulas, so silent implicit intersection is no longer necessary. Where an old formula could invisibly trigger implicit intersection, dynamic array enabled Excel shows where it would have occurred with the @.  

    Remembering that the @ symbol will only appear in “Dynamic Array Excel”, do consider carefully the ramifications of removing these pesky little @ critters. Often you can remove the @. It depends on what the part of the formula to the right of the @ returns. Before deleting, check the function/formula immediately after the @ symbol. With regard to this expression:

    • If it returns a single value (the most common case), there will be no change by removing the @ symbol.
    • If it returns a range or an array, removing the @ will cause the formula to spill into the neighbouring cells.

    If you remove the @ and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with the Ctrl+Shift+Enter braces {}): This is done to ensure previous versions of Excel will not trigger implicit intersection.

    Based on my research, there seems to be no such effective way to prevent Excel from adding implicit intersection operator automatically.

    For more information, read Implicit intersection operator: @.

    If you have any updates, feel free to post back to let us know.

    Best Regards.

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Tuesday, December 3, 2019 8:20 AM
    Moderator
  • Hi,

    I am checking the status of this issue. Do you have any update for it?

    If my reply is helpful to this question, please remember to mark it as answer. Your action would be helpful to other users who encounter the same issue and read this thread. Thanks for your understanding.

    Anything unclear or any questions, feel free to post them back to let us know. I will be glad to follow up and help you.

    Best Regards,

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.


    Monday, December 9, 2019 1:32 AM
    Moderator
  • Hi,

    This thread has been around for a long time. Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding! If you have any questions, or updates, please feel free to let me know.

    Best Regards,

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.


    13 hours 27 minutes ago
    Moderator