Need to speed up Spreadsheet

JWorthers 

Adding a macro to this large workbook is putting a bandage on a big problem and introducing a security element where it's not needed.

The way to best optimize this is to reduce the number of inputs.  The workbook posted is slow because it has a ton of scalar-based based formulas (1 cell = 1 formula).  This adds up quickly with 900,000 rows.

These are the formulas I've added.

The intervals column repeated:

=TOCOL(times*SEQUENCE(,37866,1,0),,1)

The KW column in sheet2.  The '2' in the XLOOKUP is for binary search (ascending).  Since your times are in ascending order, we can speed up the search a bit with a binary search.

=XLOOKUP(B5#,times,Sheet1!F9#,,,2)

The 'Higher/Lower' column:

=LET(
    a, DROP(C5#, -1),
    b, DROP(C5#, 1),
    IF(a > b, "Higher", "Lower")
)

The final column - I call it 'status':

=LET(
    counter, SEQUENCE(ROWS(D5#)),
    MAP(
        counter,
        D5#,
        LAMBDA(c, d,
            LET(
                status, IFERROR(TEXTJOIN("-", , CHOOSEROWS(D5#, c, c + 1)), ""),
                SWITCH(
                    status,
                    "Higher-Higher", "Really High",
                    "Higher-Lower", "Higher",
                    "Lower-Lower", "Really Low",
                    "Lower-Higher", "Lower",
                    d
                )
            )
        )
    )
)

The workbook is now an XLSX - 20 MB.

JWorthers 

Your approach is on the right track, but you might want to consider a few additional optimizations. The issue could be complex formulas, large datasets, or dependencies that are still causing delays. Here's an updated version of your code with some additional tweaks:

Vba code (is untested):

Sub CalcSheet2()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' Calculate only Sheet2
    Sheets("Sheet2").Calculate
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

In this version, I've included Application.ScreenUpdating to prevent the screen from updating during the calculation, which can improve performance.

You can assign this macro to a command button in your Excel sheet, and then run it whenever you want to manually trigger the calculation for Sheet2. This way, the calculations for Sheet2 won't be happening in the background every time there's any change on Sheet1.

Remember, the effectiveness of this depends on the nature of your calculations and formulas. If your formulas are still complex or dependent on large datasets, the calculation time might still be significant. Consider optimizing your formulas or breaking down your data into more manageable chunks if possible.

Additionally, if you are working with a very large dataset and the performance issue persists, you might want to explore more advanced techniques, such as using PivotTables, summarizing data differently, or using a database solution. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.