Forum Discussion
Help with Spill function issue
- 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!
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:
- Select the entire range of cells in column AR where the formula is present.
- Press the Delete key to clear the existing formulas from those cells.
- 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)))
- 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.
- 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.
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.
- 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!- 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))
