SOLVED

Cell settings

Copper Contributor

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. 

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! 

@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-0a11...

@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!
best response confirmed by michiel1982 (Copper Contributor)
Solution

@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.

Arrayformula.png

 

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:

MinIf formula.png

Awesome! this works, thank you so much! I highly appreciate your support.

@hynguyen Dear, one more formula you might be able to help me with. Please see the added file. Here you see my file and I have described how I need to setup the formula. Another 'if' needs to be added to the formula,  but this is a bit challenging for me. Hope you can help. Thanks again. 

@michiel1982 I can definitely help if you can clarify your requirement. Do you mean you want to have a formula in cell C9 "Sell paper", which would show:

- the lowest price in G10:G14 if cell B9 "Current price" < the lowest price in the range G10:G14? 

- the current price in cell B9 if the above condition is not met?

If so, in cell C9 "Sell paper price" put this formula:

=if($B$9<Min($G$10:$G$14),Min($G$10:$G$14),$B$9)

Similarly, in cell D9 "Buy paper price" put this formula:

= if($B$9>Max($G$10:$G$14),Max($G$10:$G$14),$B$9)

 

If my interpretation of your requirement is not correct, please give a manual result example so that I could imagine what you need.

@hynguyen Thanks for your reply. But I see now that I haven't been precise enough in my explanation, my apologies. It's a tough formula to describe :)

 

What I look for; if range H10:15 says 'order' and the order amount in G10:G15 is larger than the number in cell B9, than the 'lowest' number (0.00004) of the larger numbers (0.00004 and 0.00005) at 'order' should return, in this example the result will be 0.00004000 (cell G12).

 

The same for the lower numbers, cut I guess that this would be an easy adjustment to the formula for the above calculation. 

 

I hope this makes sense. 

@michiel1982 Give this workbook a try by playing around with your G10:H15. Currently I set the formula in C9 to return B9 value if none in column G meets your condition of being an order with lower price than B9. Similar with Buy Paper in D9 although I guess you want to use the highest of the lower prices for such case.

 

Where did you learn this level of Excel?? I thought that my level was decent.... I know that all formulas are possible, but I just don't get the full picture of how formulas are build up, like the spreadsheet you sent me. Great stuff. I will go ahead and give it a go. May any more questions occur, I know where to find you :). Thanks a million, I highly appreciate your support! 

@michiel1982 

Just in case, if to avoid array formulas, that could be

Sell:
=AGGREGATE(15,6,1/($G$10:$G$15>$B$9)/($H$10:$H$15="Order")*($G$10:$G$15),1)
Buy:
=AGGREGATE(14,6,1/($G$10:$G$15<$B$9)/($H$10:$H$15="Order")*($G$10:$G$15),1)

@hynguyen one question though. If I go to the formula you sent me and press F2 and enter. Than the number of cell B9 returns? I haven't made any changes to your formula, nor have I made any changes to the ranges. What causes my number to differ from yours? As your numbers are the correct ones I need to return as the answer. 

@Sergei Baklan thanks Sergei, I will dive into this one too. Great support :)

@michiel1982 

After editing the cell with formula @hynguyen suggested you shall use Ctrl+Shift+Enter, not Enter. These are an array formulas.

 

Of course, if your are on Excel which doesn't support dynamic arrays.

@Sergei Baklan Awesome. I add to my toolset the new Aggregate function today thanks to you. Quite handy I think

@michiel1982 yes my formulas are array ones so you need to press Ctrl-Shift-Enter while your cursor is still within the formula cell to make it work. If you want to see how it actually works step by step, I recommend using Evaluate Formula in the Formula tab of the ribbon.

1 best response

Accepted Solutions
best response confirmed by michiel1982 (Copper Contributor)
Solution

@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.

Arrayformula.png

 

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:

MinIf formula.png

View solution in original post