Forum Discussion
Excel inserts '@' Symbol into formulars, which breaks them
Hey Tech community,
I am facing the following challenge and hope someone here can explain why it is happening and how to prevent the issue:
I have created an Excel template that uses formulars to lookup values on other sheets ('Summary' & 'Master Data Merged') of the excel file based on multiple criteria.
When I type the formulars manually, they work as expected. However, when I reopen the file, excel automatically inserts '@' symbols in certain parts of the formular, which breaks it. When I manually remove the symbols from the formular again, the formular works.
The formular I am using looks like this:
=INDEX('Master Data Merged'!E:E;MATCH(1;(Summary!E4>=@'Master Data Merged'!I:I)*(Summary!F4<=@'Master Data Merged'!J:J)*(Summary!D4=@'Master Data Merged'!A:A);0))
It is used to lookup a value in column E from the Masterdata based on three criteria (FROM date on PO > FROM date in MasterData, TO date on PO < TO date on MasterData, Materialnumber from PO = Materialnumber from MasterData).
All '@' symbols have been inserted by excel after opening the file. During testing I found out, that after removing the last '@' symbol (highlighted in orange), the formular works again.
I found this documentation about the implicit intersection operator already: https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34
As far as I can tell from this post, the formulars should not break when inserting the symbol, but mine obviously are breaking. Is this a bug?
Kind regards
Felix
- What file type are you using to save the file to?
- Hey I am using .xslx files
- Thank you for the tip. So far I hadn't looked at the Options.
Unfortunately, this checkbox was already disabled.
I did some more testing myself and found that this only affects files generated in version 2019 of Excel that are then opened in Excel O365.
The formular that I used is not valid in Excel 2019, because this excel version does not support all methods I used I believe. Nevertheless, the formulars can of course still be typed.
I would assume, that once you open the same file in the O365 version of Excel, the formulars would be reevaluated.My issue seems to be the following: Excel inserts the implicit intersetion operator in O365, because that's how the formular is interpreted in Excel 2019. However the formular only works without the Implicit intersection operator. Is there a way to tell excel in the formular that I do not want the formular to be interpreted as implicit intersection?
FelixRech IN addition to what PeterBartholomew1 wrote, I would do this:
- Open the file in Excel 2019
- Find one of the cells containing the defective formula
- Make sure the entire formula is visible in the formula bar
- Clear all cells you don't want included in a screenshot
- Click Help, Feedback, I don't like something and check the screenshot box and describe your issue. Perhaps even link to this thread here. Leave your email address if you want them to contact you.
It would appear to be an error introduced in an attempt to achieve compatibility with legacy Excel.
Your formulas specify that each array is precisely 1048576 elements long and the natural implementation of the formula in old Excel would be a set of CSE array formulas over entire-column arrays.
Many Excel functions used to recognise that the calculations need not extend beyond the Used Range, but the combination of entire column references and array formulas could work out very badly in terms of performance. It seems that Excel has protected you from one problem but at the expense of invalidating your formula!
It may be that moving away from old-school entire column references to adopt the 2007 practice of using Excel Tables (or accurately sized defined Names) would bypass the problem.
JoeMcDaid should possibly get someone to check whether your problem should be considered to be a bug and, if so, whether solving it would cause more problems than leaving it.
Thank you PeterBartholomew1 and JKPieterse ! I will take both suggestions into account next time I am working on that excel file.
PeterBartholomew1 Where did you get the size of the list from? Is that just the default max list size?Kind regards
FelixThis is driving me nuts. When writing out a formula from a .NET application using the formula =SUMPRODUCT(IF(A5:A20<>"Blue",1,0),B5:B20) then open the Excel file created The formula has changed to =SUMPRODUCT(IF(@A5:A20<>"Blue",1,0),B5:B20), the addition of the @ symbol in the formula results in a broken formula returning #N/A. Remove the @symbol and the formula works . What the hell Microsoft.
Do you still get the '@' if you substitute
--(color<>"Blue") for IF(A5:A20<>"Blue",1,0)using a defined name for the array and not using IF to coerce the Booleans to values.
Thanks for the reply. When using a defined name for this I get #Value! error in the cell. The error is A value used in the formula is of the wrong datatype.
