Video instructions and help with filling out and completing When Form 2220 Consecutive

Instructions and Help about When Form 2220 Consecutive

Music 6xl youtubers as competitors for secret ingredients chosen with ran between all to compete in the excel hash yes a new video series that's going to give the Iron Chef a run for her money welcome to excel hash this is where six competitors take four secret ingredients the frequency function the max function of forum button and a 3d model and we have to take the ingredients and create an excel solution now all six of these YouTube X sellers are releasing the video on September 4th at 3 a.m. in the morning your job is to watch all six videos and then go and vote which recipe did you like the best now I'm going to take these four ingredients and mix them with Power Cree to import data will actually talk about what the frequency does and we use Max and frequency to calculate the max consecutive occurrences then we'll look at 3d models will actually do a lookup formula to look up the 3d model then we'll use the form button with a macro to update the data source and then we will refresh the data and see if everything updates as a preview let's go over here here's the data set and we just need to count which city 1 best city award most times consecutively there's our formula and guess what that's looking up the city icon on this sheet now these are 3d models they are amazing it's not a flat picture and you can click and drag and move this 3d model around anywhere and to show you the end result let's click this update it'll update the data the formulas and the lookup a 3d model should all update we click and the data set updated the formula updated with a new winner and our lookup a 3d model it all worked that is amazing now we're in the start file and we need to count most consecutive wins but first we need to import the data set and the data set is in an Excel workbook that we need to import so we're gonna go to data get and transform data is power query go up to get data from file from workbook you want to navigate minds on the desktop this is one of the files you can download double-click navigation just means hey from everything in that Excel workbook you're allowed to select the table I'm gonna click edit not load now the tables fine but one thing I want to make sure since we're counting consecutive items the sort of this really matters so it's by year that we need a sort now this came in sorted but I'm going to sort it and add a step in power crate during the import to make sure that we're safeguarding so I'm gonna click sort ascending the steps are fine the query name is fine close and load close and load to table existing a3 click ok all right so that's dynamically connected to that external workbook which will be updated now before we do our formula this is gonna be a wild array formula but we have to firmly understand what frequency does here's some numbers if we point the frequency function to these numbers and give it these upper limits it will count between each lower and upper limit now the amazing thing about frequencies we just give it the upper limits it automatically creates these categories including less than or equal to the first upper limit and one extra category that catches everything above the last upper limit we give it and sure that you notice there's the number the upper limit is included but not the lower so that 50 is counted here where it's included as the upper limit the 50 is not counted in this category this category gets only one two three all right so we highlight exactly one more cell than we have upper limits and in the active cell we type equals frequency it needs the data those are the numbers we potentially want to count between the upper and lower limit comma bins those the upper limits remember frequency will automatically create these and do all of the counting also this is an array function if I close parenthesis and before I enter it if I hit f9 i can see sure enough the definition of an array calculation is that whatever that calculation is it delivers multiple answers simultaneously control-z that's why we highlighted all the cells in advance because when we use the secret key stroke for array functions control shift enter it automatically enters all those numbers into the cell if you look up to the formula bar those curly brackets are important that's Excel telling you it understood that this was an array formula and it should be calculated as an array formula for us when we do control shift enter we're telling Excel this is an array formula the curly brackets are Excel telling us it understood and calculated it correctly now if you go to any cell you see the cell references are the same in each cell now here's the crazy thing we entered this as an array function into multiple cells but we're actually going to use the frequency in a single cell and just as we did f9 over here to evaluate we're gonna work on what frequency delivers in the cell to help us calculate most consecutive wins now the other problem we're gonna have to deal with is that is not a number these are not numbers and frequency must have numbers so before we even jump into frequency we have to convert all of the Alamitos in this column then all of the Belmonts in this column two numbers so the way we're going to do that is with the if function I'm going to ask and this is an Excel table so I