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
djclements's avatar
djclements
Silver Contributor
May 21, 2024

Aaron_Solomon In which cell was the formula entered? If it was entered in cell A2, A4 or A5, it will produce a circular reference warning (based on the sample data you've shared). Are you sure the values in range A3:E3 are correct? Should they not be A1, B1, C1, D1 and E1?

 

Having said that, the formula does not cause my version of Excel to crash, regardless of the values entered in range A3:E3. Alternatively, you could try using the MAP function in place of BYCOL:

 

=MAP(A3:E3;LAMBDA(val;INDIRECT(val)))

 

If this is still causing Excel to crash, you may want to try repairing your Office installation.

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