Excel Magic Trick 1511: Array Formula: Get Low Bid From Supplier When Delivery Less Than 20 Weeks

Download File:

In this video see how to create an Array Formula to get the low bid from Supplier when Deliver is Less Than 20 Weeks, and when the Data Set is not in Proper Data Set Form, which makes the formula a bit tricky. See the MIN and IF Functions that use Array Operations.

[excelisfun]

In Row 102, the record was incomplete and so the two formulas give a different answer. Whereas the Array Formula Directly operates on cell O102, an empty cell, and yields a zero value, the MINIFS Function (and other similar functions) treats the empty cell as nothing. The user of this solution has to decide if incomplete records are okay, or if they are not, then we would have to add a condition to the Array Formula like US, $ Column Greater than Zero.

[drsteele]

This is a super solution, Mike. It sure demonstrates the need to create raw databases as Proper Data Sets. Bad data sets like that one require time-consuming Excel gymnastics.

[sachinrv]

Great Video Mike. Frankly I have not seen many people using office 365; but sure it is of great use when you have situations like this. Superb.

[pmsocho]

So many ways to have fun with Excel :)

[sandeepkothari]

Dear Mike, Today, I bought your e-book Control+Shift+Enter. I look forward to becoming master of array formulae, like you, soon. I'm sure your book will help me become that! (Tongue in cheek!)

[SyedMuzammilMahasanShahi]

Thanks Mike for another EXCELlent video

[raymondjurado]

This video showed up at the exact right time for the problem I needed to solve on the job. Thanks!

[MySpreadsheetLab]

Another great video Mike! I really like the creative use of ranges! ...I think you meant =MIN(IF($E$2:$S$2="WKS", IF(E102:S102<$V$1, IF(C102:Q102>0, C102:Q102)))) (so that in cell T102 you get 1.15 not 0. Same for aggregate function in cell X102 =AGGREGATE(15, 6, C102:Q102/(($E$2:$S$2="WKS")*(C102:Q102>0)*(E102:S102<$V$1)), 1)

[ismailismaili]

Thanks for the video Mr. Mike this is really awesome

[faizulsalim]

Great. By the way, I have a table with customer names in one column and months in one row. If I use the max number to select the information, I get their highest sales. What formula do u recommend I use to extract the customer and month for the max number given that I can't easily lookup cause I have no row or column number?

[vinayakdalvi]

Hi Mike, Thanks for this informative video.Though you might already know this, but I tried to solve this problem using Index (Match (MINIFS in Excel 2016.The Formula is as below & this doesnt requires Ctrl+Shift+Enter
INDEX(C3:Q3, MATCH(MINIFS(C3:Q3, $D$2:$R$2, "WKS"), C3:Q3, 0)-2)
Request your opinion on this!

[dericcheung]

exel 2016, why not use maxifs/minifs ?

[markallenfuentes]

Good day sir Mike! Is it possible for me to input and get result simultaneously from another sheet?
Example,
Sheet1:
A1 [empty cell]
A2 [A1*2]
Sheet2:
((i want to input to A1 from here then get result A2 here also))
Thanks in advance good sir!

[varunk]

Hi Mike, first of all thanks a lot for this great channel.. I have been learning a lot from this.
I have come across a situation and I believe that you can help me with it, I am not sure whether this is the right place to post query like this, however I am still posting my query any help would be appreciated. below is the situation:

Col A Col B Col C Col D Col E
Criteria1 Sum1 Criteria2 Sum2 Indicator
Item_2 45 Item_3 48 Yes
Item_5 38 Item_3 24 No
Item_4 26 Item_2 43 Yes
Item_3 36 Item_2 38 No
Item_1 32 Item_4 33 No

Criteria Item_2
Indicator Yes
Sum of Criteria with Indicator 126
Formula I tried =SUMIFS(B2:D6, A2:C6, D8) *I am not able to include third citeria

the data is given in the above format which is to be like this. I need lookup Criteria in Col A & C and sum numbers from Col B & D respective to criteria and that should include indicator as well..
again any help is appreciated, thanks

[sadyaz]

I like learn array Formula from the spécialist of array Formula

[jekoo]

The old fiels with the old vedios dosent work