Report

Use of '@' in formulae [Solved]

Ask a question Quarterp 6Posts Thursday June 9, 2016Registration date June 10, 2016 Last seen - Last answered on Jun 12, 2016 02:22AM
I have received a spreadsheet with cells in column E containing the following formula
[@[Retail Price]]/[@[Pack Size]]*100
where the "Retail Price" and "Pack Size" are headings for columns C and D.
That formula is repeated exactly in all cells in column E and it does the calculation for each row based on the content of the same row cells C and D.
I've never come across the use of '@' like this and can find nothing elsewhere.
It seems to say "Find the Heading and use the contents of the cell in that column corresponding to this row".
Unfortunately, the supplier of this spreadsheet doesn't know about it and we cannot contact the author.
Can anyone shed any light on this function?
Thank you.
See more 
Helpful
+0
moins plus
Hello Quarterp,

The spread sheet must be pretty old! That method dates back to the days when Lotus 123 ruled the waves.
The @ symbol was used as the equals (=) symbol is used today in Excel which now rules the waves. Your formula appears to be referencing named ranges and should still work OK to this day. Excel does the conversion in the back ground.

The following article should explain it in more detail:-

https://www.quora.com/What-does-the-symbol-do-in-Excel

Good reading!

Cheerio,
vcoolio.
Quarterp 6Posts Thursday June 9, 2016Registration date June 10, 2016 Last seen - Jun 9, 2016 08:06AM
Hi vcoolio,
Thanks for the speedy reply.
Yes, I vaguely remember Lotus.
I know that the author of this spreadsheet was in the job where he created this from 2011 and think he was born in 1983 so I'd be surprised if he'd even heard of it. Unless it was 'passed down' to him and he changed the author to himself.
Anyway, it still doesn't make full sense since the full formula in the cells is
=IFERROR([@[Retail Price]]/[@[Pack Size]]*100,"")
meaning a mix of use of "=" and "@". Isn't the "@" redundant here anyway?
Regards
Quarterp
Reply
Quarterp 6Posts Thursday June 9, 2016Registration date June 10, 2016 Last seen - Jun 9, 2016 09:43AM
Ah - I've just found this, not that I fully understand it but I hope that you might.
https://support.office.com/en-gb/article/Use-structured-references-in-Excel-table-formulas-75fb07d3-826a-449c-b76f-363057e3d16f#__toc384648768

#This Row or @ or @[Column Name]
refers to
Just the cells in the same row as the formula. These specifiers can’t be combined with any other special item specifiers. Use them to force implicit intersection behavior for the reference or to override implicit intersection behavior and refer to single values from a column.
Excel automatically changes #This Row specifiers to the shorter @ specifier in tables that have more than one row of data. But if your table has only one row, Excel doesn’t replace the #This Row specifier, which may cause unexpected calculation results when you add more rows. To avoid calculation problems, make sure you enter multiple rows in your table before you enter any structured reference formulas.
Reply
Quarterp 6Posts Thursday June 9, 2016Registration date June 10, 2016 Last seen - Jun 9, 2016 10:10AM
and superuser.com says
"The @ is new notation in Excel 2010 replacing Row from Excel 2007. The at symbol is used to shorten formulas inside named tables referencing cells in the same row."

and http://peltiertech.com/structured-referencing-excel-tables/ says.
"The ampersand character (@) is used to identify “This Row” in a structured reference. This is also known as the implicit intersection of the row in which the reference resides".

We live and learn....I now know what use of the "@" does but am still unsure of the details. A bit of practice and experimentation is required.
Reply
Add comment
Helpful
+0
moins plus
Hello Quarterp,

You have been doing your research! Good to see.

Well, I've been educated also! But I wonder if the use of SUMIF, SUMIFS OR SUMPRODUCTS would do the task also. There's a homework exercise for you.
Maybe create a copy of your work book and try the SUMIF, SUMIFS OR SUMPRODUCT formulae. You can use named ranges in those types of functions. But then again, if your original formulae still work, why bother? It may be best to just understand them and file that understanding to the back of your mind.

Anyway, thanks for supplying the various links. It was interesting reading (I know, I need to get a life!).

Good hunting Quarterp!

Cheerio,
vcoolio.
Quarterp 6Posts Thursday June 9, 2016Registration date June 10, 2016 Last seen - Jun 10, 2016 04:17AM
OK - I'll have a look at the SUM type functions.
Thanks for your communications - much appreciated.
Reply
Add comment
Helpful
+0
moins plus
Hello Quarterp,

You'd be interested in this:-

http://www.excelforum.com/excel-general/1143200-collection-data-from-source-sheet.html

This was only yesterday. Was your original thread an omen?

Cheerio,
vcoolio.
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!