I learned something new. I didn't know the * could be used with the IF function. Question: If the user selects a capacity that is in C3:C21, why does the formula not return that number? Ex: Material = Metal, Capacity = 200.
I just answered my own question, change the second logical test operator to >=, (C3:C21>=F4). I've taken several of your courses and you are an awesome instructor! I've learned and utilized lots of advanced functions that have helped streamline and automate several repetitive tasks at work.
I am having the same problem in excel 2019. Whatever capacity or material I choose comes back with 0. I have even copied and pasted the formula just to be sure. Would love to know what I am doing incorrectly. Thank you.
Hello - Great question. The AND function will perform a comparison for each individual column but not together. If you select the cell with the formula, go the FORMULAS tab -- EVALUATE FORMULA and step through each formula. You will see the difference in how the * version and the AND version evaluates the criteria.
I don't know if you tried this on Google Sheets, but I tried (I don't have access to Excel) and it also returned as 0. So I tried to break down the formula into smaller parts and see if it was working. It's not working in same way as in the video. I tried the logical validation of the first part "(B3:B21=E4)" and it came back as false. Sheets interpret it as ALL values from B3:B21 must be equal to E4. And that's why this elegant formula doesn't work at Sheets.
I learned something new. I didn't know the * could be used with the IF function. Question: If the user selects a capacity that is in C3:C21, why does the formula not return that number? Ex: Material = Metal, Capacity = 200.
I just answered my own question, change the second logical test operator to >=, (C3:C21>=F4). I've taken several of your courses and you are an awesome instructor! I've learned and utilized lots of advanced functions that have helped streamline and automate several repetitive tasks at work.
Great glad you found the solution!
Very nice 👍
Thank you!
I am having the same problem in excel 2019. Whatever capacity or material I choose comes back with 0. I have even copied and pasted the formula just to be sure. Would love to know what I am doing incorrectly. Thank you.
We would need to be able to see your data and formula.
Can we use AND function instead of * ?
Hello - Great question. The AND function will perform a comparison for each individual column but not together. If you select the cell with the formula, go the FORMULAS tab -- EVALUATE FORMULA and step through each formula. You will see the difference in how the * version and the AND version evaluates the criteria.
I tried this formula but the value comes out as Zero. Can you explain me why it comes?
Just to make sure you type this formula?
=MIN(IF((B3:B21=E4)*(C3:C21>F4),C3:C21))
@@OfficeNewbYes I typed the same formula in the same file that you have provided. Is there any changes do I need to do in the Excel?
I don't know if you tried this on Google Sheets, but I tried (I don't have access to Excel) and it also returned as 0.
So I tried to break down the formula into smaller parts and see if it was working. It's not working in same way as in the video.
I tried the logical validation of the first part "(B3:B21=E4)" and it came back as false. Sheets interpret it as ALL values from B3:B21 must be equal to E4.
And that's why this elegant formula doesn't work at Sheets.