Forum Discussion

RameshYedhula's avatar
RameshYedhula
Copper Contributor
Dec 11, 2022

Issue with excel 365 for Mac - some formulas are not working

Hi Greetings

 

I have excel 365 and using in Mac, some of the formulas like SORT, FILTER etc are not working. in the formula cells text "=INDEX_xlfn.ANCHORARRAY(", _xlfn._xlws.SORT(" , "=_xlfn._xlws.FILTER(" "=XLOOKUP(A8,_xlfn.ANCHORARRAY('" etc are getting added automatically. 

 

Can someone help in resolving this issue.

 

Regards

Ramesh

Excel on Mac
office 365
  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Dec 11, 2022

    RameshYedhula  A function like ANCHORARRAY() is not needed in Excel, because Excel automatically recognises array formulas and also automatically spills results if necessary.

    Excel cannot use the ANCHORARRAY function when it is opened and "translates" incorrectly.

    Your formula should look something like this in Excel:

    =INDEX(1,SORT(FILTER(A1:A100,B1:B100=XLOOKUP(A8, etc

    Since you have not published your complete formula here, the function given can only be an example. The way you wrote it down seems to be the completely wrong syntax.

    • RameshYedhula's avatar
      RameshYedhula
      Copper Contributor
      Dec 11, 2022
      dscheikey, HansVogelaar

      Thanks for your reply, I didn't use the "ANCHORARRAY" function, neither is worked in the "GrapeCity SPREAD.NET?" which you have referred.

      these ANCHORARRAY automatically popped up in the formula bar, this happened when I opened my excel file after my MacOs was upgraded to Ventura. this is what is confusing me. I am not able to remove this. pls find below the formula what I had used earlier and what is appearing now. also there is a {} appearing now

      formula prior: =INDEX('Current Holding'!$A$6#;MATCH(Dashboard!A16;'Current Holding'!$L$6:$L$250;0))

      formula appearing now: {=INDEX(_xlfn.ANCHORARRAY('Current Holding'!$A$6),MATCH(Dashboard!A17,'Current Holding'!$L$6:$L$300,0))}
      similarly other examples
      Eg 1:
      Prior:=SORT(STOCKHISTORY(B3;G3;TODAY();2;0;0;1);1;-1)
      after: =_xlfn._xlws.SORT(STOCKHISTORY(B3,G3,TODAY(),2,0,0,1),1,-1)
      Eg 2
      prior: =SORT(UNIQUE(FILTER(Stocks[Demat Stock Name];SUMIF(Stocks[Demat Stock Name];Stocks[Demat Stock Name];Stocks[Qty])<>0)))
      after upgrade: {=_xlfn._xlws.SORT(_xlfn.UNIQUE(_xlfn._xlws.FILTER(Stocks[Demat Stock Name],SUMIF(Stocks[Demat Stock Name],Stocks[Demat Stock Name],Stocks[Qty])<>0)))}
      Eg3
      Prior: =FILTER('Total Holdings'!B9#;'Total Holdings'!C9#>0)
      after: {=_xlfn._xlws.FILTER(_xlfn.ANCHORARRAY('Total Holdings'!B9),_xlfn.ANCHORARRAY('Total Holdings'!C9)>0)}
      • peiyezhu's avatar
        peiyezhu
        Bronze Contributor
        Dec 12, 2022
        $A$6#;
        ??
        $A$6,


        have excel 365 and using in Mac,
        FILTER
        365 not support this function on Mac?

        if so,I guess you need to find another way.

Resources