Jun 11 2023 04:22 PM
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?
Jun 11 2023 04:35 PM
Jun 11 2023 10:29 PM
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:
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.
Jun 11 2023 10:35 PM
@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.
Jun 11 2023 10:44 PM
SolutionJun 11 2023 10:47 PM
Jun 12 2023 05:19 AM
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()))))
)
Jun 11 2023 10:44 PM
Solution