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

Occasional Contributor

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

4 Replies

@RameshYedhula 

ANCHORARRAY is not an Excel function. Could the workbook have been created (or edited) in GrapeCity SPREAD.NET?

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

@dscheikey, @Hans Vogelaar

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)}
$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.