Forum Discussion
Cell settings
Dear, I have a formula in excel, the formula works (below the full formula). When I copy this formula to certain cells of my sheet than I get #values! back. I know excel quiet a bit, but can't figure this one out, takes me hours already :). Format Cell is set to 'General'
'=min(if(range="text";values))
I use Microsoft Office Professional Plus 2019
Thank you for your support.
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:
18 Replies
When I click on the fx button, Excel says that the formula result is 10. Which is the outcome of my formula and is correct. Still the cell returns #value!.
michiel1982 Some suggestions:
1. =min(if(range="text";values)) → Change this semi-colon to comma
2. Are you using absolute/named range or relative range? Because it is possible when you copy the formula to somewhere else, the range changes if it is not fixed
3. Consider using MINIFS if applicable
4. Look like you use a range (not a cell) in IF function. Perhaps you must use array formula (CTRL + SHIFT + ENTER) after typing in the formula to make it work
5. Select the cell where you have the formula with error, go to Formula Tab/Evaluate formula, then click Evaluate button until you see errors showing in the panel. It would help you audit your formula and find where things go wrong
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!
