Jan 20 2023 12:51 AM
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)
Jan 21 2023 03:54 PM
Jan 23 2023 09:06 AM - edited Jan 26 2023 03:39 AM
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.