Forum Discussion

Aaron_Solomon's avatar
Aaron_Solomon
Copper Contributor
May 20, 2024

Excel crashing when combining BYCOL and INDIRECT

Following recent updates (Excel latest version and/or MacOS 14.5 ?), Excel crashes systematically on Mac OS 14.5, with a simple formula using BYCOL & INDIRECT: =BYCOL(A3:E3;LAMBDA(col;INDIRECT(INDEX...
Excel on Mac
Formulas and Functions
Aaron_Solomon's avatar
Aaron_Solomon
Copper Contributor
May 21, 2024
djclements, thank you for your reply, you are correct the values in range A3:E3 should be A1 - E1. The formula still lets excel crash. And if I remove the INDEX(col;1) and replace it by col I get a message from Excel "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated."
I also tried your alternative with MAP (as well as with SCAN), both have the same results : crash with INDEX, and lack of resources without INDEX.
djclements's avatar
djclements
Silver Contributor
May 21, 2024

Aaron_Solomon Since you're getting the "Excel ran out of resources" error message, a good place to start would be to check the current memory usage on your system using the Activity Monitor (see: https://support.apple.com/en-ca/guide/activity-monitor/actmntr1004/mac ).

 

You could try to free up memory by closing any apps running in the background that you currently don't need open (see: https://support.apple.com/en-ca/guide/activity-monitor/actmntr1002/mac ).

  • Aaron_Solomon's avatar
    Aaron_Solomon
    Copper Contributor
    May 22, 2024

    djclements, I checked the memory usage in the Activity Monitor, before loading the file, after loading the file, after un-commenting the formula so that Excel executes it : Excel memory usage never exceeds 1GByte. My Mac has 64Gbytes Ram and it seems plenty is available (>> 1 GByte), here is a screen capture of my Activity Monitor for the memory usage.

    • Aaron_Solomon's avatar
      Aaron_Solomon
      Copper Contributor
      May 22, 2024

      djclements, I checked on internet, it seems other are having the same issue. Following the recommendation provided on below thread where it's stated that similar problems are know issue with Excel 16.85, I downgraded to Excel 16.84 and the problem no more occurs. Thanks for your support !
      https://answers.microsoft.com/en-us/msoffice/forum/all/excel-crashes-on-certains-workbook-on-macos/ab3e2c65-a8c1-408d-bcc9-c9d523144eab?messageId=cbe94670-4c08-4f54-aa95-4daa1ea35a7b 

Resources