Forum Discussion
Puzzling performance issues with LAMBDA array formulas
Link to Excel file: https://1drv.ms/x/s!Ai-HOVsIDqunfOkiOHKlouSLQq4?e=cybHjH I have an Excel workbook with 30 array formulas. Each formula is a named lambda function, which outputs a 1-column x 5...
Very interesting example. My first guess was the calculations are slow because Excel is doing a few things when copying/pasting formulas to a new sheet:
1. Construction of a dependency tree
2. Construction of a calculation chain
3. Recalculation of cells
https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation
I performed the slow version of the copy/paste and then saved the workbook and converted it to a zip. I inspected calcChain.xml and noticed it had nearly doubled in size.
Opened the workbook again and performed the copy/paste by selecting only E3:AH3. Once again, I saved and closed the workbook. I converted it to a zip and the calcChain.xml file was identical to the calcChain post-slow/copy paste.
I did not test all your functions but I did play with storing SEQUENCE in a named item and calling it in the formula. The slow method was still slow.
My computer is only about 6 months old. Decent processor, 32 GB RAM, 4K screen. Excel 365 (Version 2302 - Beta Channel). I suspect it may be a rendering issue but would like to see how these tests run on other computers.
1. Construction of a dependency tree
2. Construction of a calculation chain
3. Recalculation of cells
https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation
I performed the slow version of the copy/paste and then saved the workbook and converted it to a zip. I inspected calcChain.xml and noticed it had nearly doubled in size.
Opened the workbook again and performed the copy/paste by selecting only E3:AH3. Once again, I saved and closed the workbook. I converted it to a zip and the calcChain.xml file was identical to the calcChain post-slow/copy paste.
I did not test all your functions but I did play with storing SEQUENCE in a named item and calling it in the formula. The slow method was still slow.
My computer is only about 6 months old. Decent processor, 32 GB RAM, 4K screen. Excel 365 (Version 2302 - Beta Channel). I suspect it may be a rendering issue but would like to see how these tests run on other computers.
My laptop s older (4 years) with 8GB RAM and 1920x1080 display. What is strange is the difference between copying in the same sheet and in another sheet. The dependency graph is very simple, so it should not take any substantial amount of time to calculate.
I also checked in Excel Online: the timing pattern is similar, but the performance penalty for pasting the entire range on a different sheet is lower.
