Use of '@' in formulae

Solved

Excel

Quarterp Posts 6 Registration date Thursday June 9, 2016 Status Member Last seen June 10, 2016 - Updated on Sep 16, 2019 at 03:54 AM
Quarterp Posts 6 Registration date Thursday June 9, 2016 Status Member Last seen June 10, 2016 - Jun 10, 2016 at 04:17 AM

Hi,

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.

Excel @ in formula
Using @ in excel - Best answers
Using $ in excel - Best answers
Position formula in excel ✓ - Office Software Forum
Date formula in excel dd/mm/yyyy - Guide
How to calculate position (1st,2nd,3rd) in excel....? - Excel Forum
Calculation of positions in excel ✓ - Excel Forum
Formulas for 1st and 2nd position in class - Office Software Forum

2 responses

Answer 1 / 2

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jun 10, 2016 at 01:43 AM

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 Posts 6 Registration date Thursday June 9, 2016 Status Member Last seen June 10, 2016
Jun 10, 2016 at 04:17 AM

OK - I'll have a look at the SUM type functions.
Thanks for your communications - much appreciated.

Answer 2 / 2

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Updated on Sep 9, 2019 at 08:09 AM

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.

Good reading!

Cheerio,
vcoolio.

Quarterp Posts 6 Registration date Thursday June 9, 2016 Status Member Last seen June 10, 2016
Jun 9, 2016 at 08:06 AM

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

Quarterp Posts 6 Registration date Thursday June 9, 2016 Status Member Last seen June 10, 2016
Jun 9, 2016 at 09:43 AM

Ah - I've just found this, not that I fully understand it but I hope that you might.
https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e?redirectsourcepath=%252fen-gb%252farticle%252fuse-structured-references-in-excel-table-formulas-75fb07d3-826a-449c-b76f-363057e3d16f&ui=en-us&rs=en-us&ad=us#__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.

Quarterp Posts 6 Registration date Thursday June 9, 2016 Status Member Last seen June 10, 2016
Jun 9, 2016 at 10:10 AM

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 https://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.

Similar discussions

@ symbol doesn't work, it comes up as a "

How do I type the @ on a British keyboard?

Cannot type @ on my acer laptop.

Can't type @ symbol on Acer laptop

Use the @ symbol on Acer Aspire

How can I type @ on my Acer Laptop

Use of '@' in formulae

2 responses

Your reply

Similar discussions

Subscribe To Our Newsletter!