Forum Discussion

FelixRech's avatar
FelixRech
Copper Contributor
Nov 18, 2022

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

    • FelixRech's avatar
      FelixRech
      Copper Contributor
      Thank you for the tip. So far I hadn't looked at the Options.
      Unfortunately, this checkbox was already disabled.
  • FelixRech's avatar
    FelixRech
    Copper Contributor

    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?

     

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor

      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.

  • FelixRech 

    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.

     

  • NickFranklin's avatar
    NickFranklin
    Copper Contributor

    This 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.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor

      NickFranklin 

      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.

      • NickFranklin's avatar
        NickFranklin
        Copper Contributor

        PeterBartholomew1 

         

        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.

Resources