Forum Discussion

JoNC_NZ293's avatar
JoNC_NZ293
Copper Contributor
Jun 11, 2023
Solved

Help with Spill function issue

Hi all   I have inherited this spreadsheet and I need to adjust the threshold that is used in the calculation. It seems to be a simple change but I am getting a #SPILL error that I can't resolve. ...
excel
  • NikolinoDE's avatar
    NikolinoDE
    Jun 12, 2023
    By simplifying it to =MIN(IF(AF6:AQ6>2.5,AF6:AQ6), you are still using the MIN function with an array formula, but without the spill behavior. It will return the minimum value from the range AF6:AQ6 that is above the threshold of 2.5.
    If the simplified formula meets your requirements and provides accurate results, then you can continue using it. Remember to adjust the threshold value as needed.
    I wish you continued success with Excel!
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 12, 2023

JoNC_NZ293 

In general that could be done in one spill. Not sure how Optimal Item is calculated, if previous two

having

for the

formula could be like

=LET(
    cleanData, TAKE(
        data,
        MAX(BYROW(data, LAMBDA(v, SUM(v) > 0)) * SEQUENCE(ROWS(data)))
    ),
    rowsCalc, BYROW(
        cleanData,
        LAMBDA(v,
            IFERROR(
                LET(
                    bestRatio, IFERROR(
                        MIN(FILTER(v, (v <> 0) * (v >= threshold))),
                        ""
                    ),
                    bestTime, IFERROR(XLOOKUP(bestRatio, v, time), ""),
                    LAMBDA(HSTACK(bestRatio, bestTime))
                ),
                ""
            )
        )
    ),
    n, ROWS(rowsCalc),
    first, INDEX(rowsCalc, 1, 1)(),
    rest, INDEX(rowsCalc, SEQUENCE(n - 1, , 2)),
    IF(n = 1, first, REDUCE(first, rest, LAMBDA(a,v, VSTACK(a, v()))))
)

Resources