Ampersands Appearing in Formulas

Justinian

Well-known Member
I have recently been experiencing something I have not seen in my fifteen years of working with Excel: @ signs are appearing in my workbooks formulas directly after the = sign. For example:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=@IF(COUNTA(C6:AG6)=0,"",IF(COUNTIF(C6:AG6,"?*"),SUM(IF(LEFT(C6:AG6&"|",2)="S|",8,IF(1-(C6:AG6=""),IF(LEFT(C6:AG6)="S",REPLACE(C6:AG6,1,1,"")+0))))))

Why are ampersands being inserted into my formulas?
[/FONT]
 

jmacleary

Well-known Member
Hi there. The @ is new notation in Excel 2010 and onwards replacing [#This Row] from Excel 2007. It is used to shorten formulas inside named tables referencing cells in the same row. It is also (I think) a compatibility option with lotus 123.

PS Its an 'at symbol' not ampersand(&).
 
Last edited:

RoryA

MrExcel MVP, Moderator
When the @ symbol appears before a function, it's because the function uses implicit intersection, and this doesn't fit with the new dynamic array formulas (@ is actually a replacement for the briefly introduced SINGLE function).
 

Justinian

Well-known Member
Weird. I have been using this version of Excel for a few months now but only in the last two or three weeks did these @ symbols start appearing. Why is that?
 

RoryA

MrExcel MVP, Moderator
It's new functionality and it gets rolled out gradually, depending on what update channel you're on.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top