1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • @ symbol automatically showing up in formula

    From David.cuthill@perf.com@21:1/5 to All on Thu Oct 15 20:06:43 2020
    I am writing an Excel formula from a Matlab script into a empty excel sheet and excel seems to want to apply an @ symbol at various points in the formula and then when I try to copy the formula down I get an error. Why does Excel feel it needs to add
    this character and how do I prevent it from showing up. In attempting to copy this formula down results in the #N/A being triggered - if i manually remove the @ symbols the formula copies downward fine and works as intended.

    I am open to any ideas - other formulas that I write from Matlab do not result in this added character - only this one. I am running Excel for Office 365

    =IFERROR(@INDEX($Q:$Q,SMALL(IF(@$O$3:$O$32=AA$2,@ROW($3:$32)),ROW(B1))),NA())

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David.cuthill@perf.com@21:1/5 to All on Thu Oct 15 23:05:02 2020
    I have been doing some additional research and it appears to be related to improved functionality (https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34?ui=en-us&rs=en-us&ad=us). Excel seems to be
    interpreting the formulas intent differently than the original intent as it was entered. Any idea how this can be overridden?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David.cuthill@perf.com@21:1/5 to david....@perf.com on Fri Oct 16 12:14:42 2020
    On Friday, 16 October 2020 00:05:06 UTC-6, david....@perf.com wrote:
    I have been doing some additional research and it appears to be related to improved functionality (https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34?ui=en-us&rs=en-us&ad=us). Excel seems to
    be interpreting the formulas intent differently than the original intent as it was entered. Any idea how this can be overridden?

    One other thought - maybe the original formula needs to be rewritten so that Excel interprets it correctly so that when it is copied downward it behaves as expected.

    Original formula (written into the sheet from Matlab as a string) - based on an earlier post --- https://groups.google.com/forum/#!topic/microsoft.public.excel/DF57UsZfTjA

    =IFERROR(INDEX(Q:$Q,SMALL(IF($O$3:$O$32=AA$2,ROW($3:$32)),ROW(B1))),NA())



    Excel's reinterpretation =IFERROR(@INDEX($Q:$Q,SMALL(IF(@$O$3:$O$32=AA$2,@ROW($3:$32)),ROW(B1))),NA())


    What would I change in the 'string' so that Excel interprets it correctly.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)