Help with Spill function issue

[JoNC_NZ293]

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.

Device and OS platform, pc windows 10 pro
Excel product name and version number
Microsoft® Excel® for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit

Excel file you’re working with or sample file with data – not sure how to attach?
What I am trying to do I'm trying to adjust the formula in column AR (which is =IF((AF6:AQ6<1.5),0,MIN(IF((AF6:AQ6>=1.5),AF6:AQ6,10^99)))) to use the values 2.5 instead of 1.5 - this is the ONLY thing I have changed (I'd actually like to pull the value from another cell eventually but i can see if I can solve this first). When I edit it I get a #SPILL error saying the spill range isn't blank. I have read up on this but can't really see how to solve this and why it is happening given the formula in the cell (see green). Even if I edit it back to be the old value I still get an error so I am wondering if there has been some Excel functionality change since it was created???

Expected result I want to be have the formula adjust to use 2.5 instead of 1.5 and in all other ways be the same 
Can anyone help please?

[#2]

p.s. I tried to add the file, but I got an error saying .xlsx files are not supported? so confused....

[#3]

@JoNC_NZ293 

The #SPILL error in Excel occurs when a formula that is supposed to spill over multiple cells encounters an issue. In your case, it seems that the formula in column AR is producing the #SPILL error when you change the threshold value from 1.5 to 2.5.

To resolve this issue, you can try the following steps:

1. Select the entire range of cells in column AR where the formula is present.
2. Press the Delete key to clear the existing formulas from those cells.
3. Enter the following formula in the first cell of the range (AR6): =IF((AF6:AQ6<2.5),0,MIN(IF((AF6:AQ6>=2.5),AF6:AQ6,10^99)))
4. Instead of pressing Enter to finish entering the formula, use the key combination Ctrl + Shift + Enter. This will enter the formula as an array formula.
5. The formula should now spill over the selected range without producing the #SPILL error.

By using Ctrl + Shift + Enter, you are confirming that the formula is an array formula and allowing it to calculate the result for the entire range.

If you still encounter the #SPILL error after following these steps, it's possible that there are other factors causing the issue. In that case, it would be helpful to see the actual spreadsheet or a sample file with the data.

[#4]

@NikolinoDE thank you very much for your feedback. That is interesting about how to enter the array formula key combination... I will file that in my memory banks.

For my example I only wanted a single value being the minimum value above my threshold, so I didn't want it to spill into the subsequent columns. I got around the problem in the end by simplifying the equation to =MIN(IF(AF6:AQ6>2.5,AF6:AQ6)

This seemed to do the job when I compare to the original spreadsheet I was supplied and adjusted the number back to 1.5, the figures matched.

[#5 · Accepted solution]

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!

[#6]

Yes that is what I need. I'm not sure why it was set up as it was when only a single value was required.
And yes i have adjusted the threshold to use a value from elsewhere in the sheet so that I can adjust it as needed, ie. =MIN(IF(AF6:AQ6>=$I$1,AF6:AQ6))

[#7]

@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()))))
)