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:
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:
SergeiBaklan 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.
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.
SergeiBaklan thanks Sergei, I will dive into this one too. Great support 🙂
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.
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)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 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.
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 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 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.
Awesome! this works, thank you so much! I highly appreciate your support.
.png){kind=link}
.png){kind=link}