Puzzling performance issues with LAMBDA array formulas

Copper Contributor

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 512-rows array. The formulas are computationally expensive, but not that much (it is a fast Fourier transform). Recalculation of all formulas takes a fraction of a second.


In the abridged example linked from this post, the formulas are in cells E3:AH3, the total range occupied by the results is E3:AH514


Performance puzzle 1

Copying just the formulas (E3:AH3) and pasting them elsewhere, on the same sheet or on another sheet, is very fast (no perceptible delay).
Copying the entire range (E3:AH14) and pasting it on the same sheet is also very fast.
Pasting the range on a different sheet is very slow (> 5 seconds). In fact I can see Excel filling one column at a time. Same when duplicating the sheet.

Performance puzzle 2

The formula has two different calculation methods for the FFT, radix-2 if the length is a power of two, and Bluestein's algorithm otherwise. In this case the length is 512, therefore the Bluestein algorithm is never applied. However, replacing the function call to Bluestein with a #VALUE! error speeds up the code considerably (still not enough to fix the puzzle 1 above). 

In the linked workbook, this can be seen by replacing the function FFT with FFT_alt in E3:AH3.


Does anybody have an idea about the causes of this performance issues?


(Windows 10 / Microsoft® Excel® for Microsoft 365 MSO (Version 2212 Build 16.0.15928.20196) 64-bit)

2 Replies
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


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.