Use of '@' in formulae

Solved

Excel

Quarterp Posts 6 Registration date Thursday 9 June 2016 Status Member Last seen 10 June 2016 - 9 Jun 2016 à 04:28
Quarterp Posts 6 Registration date Thursday 9 June 2016 Status Member Last seen 10 June 2016 - 10 Jun 2016 à 04:17

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.

Summertime saga downloadable content
Summertime saga apk - Best answers
Formulas and functions in excel make use of cell - Best answers
Student position formula in excel ✓ - Office Software Forum
Spreadsheet formulae use - Guide
Number to words in excel formula - Guide
How to calculate position (1st,2nd,3rd) in excel....? - Excel Forum
Calculation of positions in excel ✓ - Excel Forum

2 responses

Réponse 1 / 2

vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Moderator Last seen 6 September 2024 262
10 Jun 2016 à 01:43

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 9 June 2016 Status Member Last seen 10 June 2016
10 Jun 2016 à 04:17

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

Réponse 2 / 2

vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Moderator Last seen 6 September 2024 262
9 Jun 2016 à 07:18

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 9 June 2016 Status Member Last seen 10 June 2016
9 Jun 2016 à 08:06

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 9 June 2016 Status Member Last seen 10 June 2016
9 Jun 2016 à 09:43

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 9 June 2016 Status Member Last seen 10 June 2016
9 Jun 2016 à 10:10

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

Similar discussions

Subscribe To Our Newsletter!