Forum Discussion
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. ...
- 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!
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!
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))