Forum Discussion
Cell settings
michiel1982 The screenshot makes the troubleshooting much easier. I guess you want to find the lowest value in column K if column H matches your desired product "Paper". Because you use a range in IF statement instead of a single cell, you will have to use CTRL + SHIFT + ENTER after typing your formula (while your cursor remains in the cell) so that there is curly bracket { } wrapping around your formula to make it an array one.
Please see the screenshot. Just a single Min(if) array formula should return your desired outcome. Note that I test with adding another Paper at the end of the range to see if it returns the right result.
If you have MINIFS function available in Excel, I suggest you use it instead of array formula. See its use in the below screenshot with same result:
Thanks for your support!
I tried your options, but these don't work unfortunately. My challenge is that in one cell of the same sheet, the formula works just fine. When I copy this exact same formula, using the exact same range, and paste the formula in a different cell, than I get the #value! error.
Something seems to be wrong with the settings of that particular cell, but I can't figure out what it is. I also copy and pasted the cell formats. When I open a new sheet, and try to do the same, there are 3 rows in which it works perfectly, all other cells give this error.
Hope this clarifies a bit better, thanks again, hope to hear!
michiel1982 It seems difficult to imagine what the issue is without actually seeing your example. When you copy your formula to another cell and have the error, and you Evaluate the formula (Click Formulas > Evaluate Formula > Evaluate. Excel will step through the parts of the formula individually), can you take screenshot of the step immediately before getting your #Value error?
You can read this to have an idea of how to troubleshoot your formula:
https://support.microsoft.com/en-us/office/how-to-correct-a-value-error-15e1b616-fbf2-4147-9c0b-0a11a20e409e
hynguyen thanks for your support. Please find attached two print screens as you requested. Also, one print screen has a green and a red dot in it, the red dot is where the formula does not work, the green dot is where the formula works perfectly. Hope to hear, thanks!
- by the way, the formula is still incorrect as the number that I should bet in return is 10, not 5. But that is something else to figure out 🙂 I first want to get writ of the #value!
michiel1982 The screenshot makes the troubleshooting much easier. I guess you want to find the lowest value in column K if column H matches your desired product "Paper". Because you use a range in IF statement instead of a single cell, you will have to use CTRL + SHIFT + ENTER after typing your formula (while your cursor remains in the cell) so that there is curly bracket { } wrapping around your formula to make it an array one.
Please see the screenshot. Just a single Min(if) array formula should return your desired outcome. Note that I test with adding another Paper at the end of the range to see if it returns the right result.
If you have MINIFS function available in Excel, I suggest you use it instead of array formula. See its use in the below screenshot with same result:
_LI.jpg){kind=link}
.png){kind=link}