Excel Magic Trick 627: FREQUENCY Array Function (10 Examples)

welcome to exile my trick number 627 hey if you want to download this workbook and follow along click on my youtube channel then click on my college website link in you can download the workbook at symmetric 6:27 oh wow this is going to be an epic video probably the the longest most epic video I have done to date we're going to talk about the frequency function from beginning to end the basics of it what it does all the aspects of it and then we're going to look at all the way to the end 10 examples getting more and more complicated as we go through this video so what does the frequency function do it counts given some bins or upper limits elements so all we're going to do is create a frequency distribution let's go look at an example here there's our upper limits here are the categories and the frequency function we'll talk about this very important if you give it an upper limit of 10,000 as your first bin when it goes over to this column here and counts the amounts that are less than or equal to 10,000 that's the first bin and it's different than the rest here we get a count of 0 if you give it the second bin of 20,000 that means there's an increment of 10,000 between these this line right here the frequency function in that cell right there will count between this one greater than this one sort of mount greater than the bin from before but less than or equal to the current upper limit bin so it does that all the way down when it gets to the end it's going to have one extra category we give it this many bins it has one extra category it says for the last category any amount above that last bin now the reason why it does that is so it can be robust if we didn't have this last category and someone accidentally had a value here or didn't include a upper bin that was high enough then we get a miscount so it's very robust because the first category everything below the last category there's always one extra and it's thing above now let's see how to do this now when we have a data set here's some invoice numbers and amounts here there's lots of way to figure out the bins and in my statistics class I talk about some methods but at minimum at least get the minimum and the max from your data set so all I did was say hey what's the min in that data set and what's the max and then build your categories your bins your upper levels so that all values are included so I'm going to start at 10000 I'm going to hit enter and then put 20,000 these two numbers have established a pattern and then I drag it down so I get 10,000 right so this is exactly what the categories will look like very important now I have some formulas here that are calculating these if you want to download this and look at it sometimes for people that don't understand how what's going on these categories are necessary oftentimes they're not you just say hey these are the upper limits these two aspects right here usually are not understood by someone looking at it it doesn't understand it so sometimes categories can be nice now frequency function let's look at some notes over here before we use it we need bin arrays those are the upper values data array that's the argument that's the name of it those are just the values to count frequency function ignores empty cells and text now we'll use that to our advantage when we get to a later example when we're counting unique numbers frequency function always returns one more category than you give it that's to include that upper value like we talked about it always delivers a vertical array so if you want to display your frequency distribution horizontally we're going to have to see a way to get around that this is an array function so there's two things that must be true you must highlight all the cells in advance when you're delivering an array of numbers for your frequency and you have to use control shift enter not only that but if there are duplicate bins which in normal uses you don't have duplicate bins but if you do in our count unique example we're going to take Vantage of this if there's duplicates the duplicates will get a count of zero and we'll see an example of that all right here we go we have our bins we highlight the cells one more than the number of bins we type notice we've highlighted in advance all the cells so each cell will get a different number a different count depending on the Associated category so this one will get whatever the category defined by this upper limit is in that cell active cell at the top so you type equals frequency first it wants the data I go over and get this control shift down I'm going to hit f4 f4 f4 notice if we had some dollar signs or if we don't relative cell reference totally irrelevant when you're entering the frequency function into a bunch of cells and we'll see that when we enter this P 2 to P 32 will be entered into all of these cells so it's not like a normal formula with normal cell references like absolute or relative bins control shift down arrow close parentheses and now I use control shift and enter to enter all of the for most notice here P 2 2 P 32 notice here P 2 2 P 32 this is an array you see the can the curly brackets there that means we've entered it with ctrl shift enter it does require that if you try to delete something in the middle of it it says cannot change part of the array you have to change the array all at once because it was entered in all together so that's the basics of how to use the frequency function now in some cases like I teach statistics we do not want categories like this and so we want categories like greater than or equal to 30,000 but less than the upper limit if that's the case which is sometimes the case depending on your chart criteria or frequency distribution criteria you cannot use the frequency function because the frequency will always include the upper limit you have to switch to like count if function or some product or something like that and I have other videos on that now notice next aspect of the frequency function notice we entered into a vertical array of cells let's just see what happens if we were to I'm going to scoop this out of here scoop this out of here copy and then escape what if we wanted it horizontal not vertical I'm going to come over here I have the same kind of setup here actually I'm just going to redo it here one more cell then the bins equals frequency I want the data I'm going to click there control shift down oh f 4 f 4 f 4 comma and then I need my bins here F 4 F 4 and 4 alright so if I highlight this and hit the f9 key please notice that's called array syntax curly brackets semicolons mean row that means row row row that's a vertical array if there were commas it would be a horizontal array and it would represent columns so I'm going to control Z what happens if we control shift enter we get 0 and all that's happening is it because we've listed it this way it can only list the very first item from the vertical array so what do we need to do because there were I'm going to hit f2 because these are semicolons we need to convert them to commas no problem we don't need to really know that semicolons are rows and commas or columns ctrl Z because we can just use the transpose function now the transpose function is exactly like the frequency in that it is an array function and it must be entered with ctrl shift enter all transpose does is take all the rows and turn them to columns and vice versa control shift enter and now we have our frequency distribution orientated horizontally transpose of the frequency because frequency only delivers a vertical or rate we're going to run into this problem late in our last example also alright neck topic before we start going most of the rest of the topics are all sorts examples of count unique and how the frequency function is really the best method for a counting unique now I want to look at an example of something first I'm going to highlight oh and our right-click insert smart tag clear formatting and I'm going to make it really small here oops wrong one ctrl Z I'm going to make Q really small I always leave a little blank columns because if I ever sort or do anything the data set can't touch anything else or else it messes up the sort now we talked about the fact that frequency function delivers a vertical array but next we want to talk about counting unique items whether they are numbers or words or other fit items let's go ahead and just look at what happens if we highlight a range that is exactly the same size as all the data items in the active cell at the top I'm going to type frequency data array well I'm going to highlight this control shift down f4 comment now bins in our last couple examples we highlighted this but what would happen if we actually highlight the same range there again well think about this what is it going to do there's a duplicate right there and there's a duplicate right there and there's actually another exact duplicate down below now remember over in our notes we mentioned that if there are duplicate bins which we are given that right now the second one will not get account it will count zero the first bin is the only one that counts so if there are three entries in this list of this which means there's one unique item in two duplicates this bin right here represented in this somewhere in here will get a count of three for all of them but this one will get a zero and the one below will get a zero let's just prove this to ourself by entering this in control shift enter one that means it's unique this number right here has two other duplicates one for the unique and two for the duplicates and look down here we get a zero and finally let's find the third one - if I can find it there it is we get a zero so how could we use this knowledge here we get a return list of numbers and the zeros are the ones that are duplicates all the time there's a number greater than zero that means we need to count that so we need to go one two three four not count that one five no problem we can just use this same little thing right here I'm actually going to copy this ctrl C and come over here and we're going to make a formula to count unique numbers this is actually count unique numbers equals control V now right now if we highlight this and hit the f9 we can see we get that exact list of numbers but in a single cell if we entered it right now it would just display the very first one control Z but what if we said a greater than 0 now we highlight this and hit f9 now we got a bunch of trues and falses so true true true means this is unique false means eight that's a duplicate control Z how do we convert those trues and falses to ones and zeros we put parentheses around it and we put a double negative why the the parentheses because this is a unitary operator the negation symbol and it comes is calculated before the greater than symbol which is a comparative operator so to force the comparative operator to calculate before the double negative we put it in parentheses now the double negative will turn the trues and falses to ones and zeros f9 now we simply control Z and put our sum product that will count those ones and 0 so we get a count of 17 so that's how to count unique numbers now one interesting aspect of County unique numbers because the frequency function ignores blank cells we can actually count if we had any blanks here it would count clay there's still a lot let's let's remove I'm so I control Z I'm going to remove a unique one right there now we should get a count of 16 sure enough we do so control Z if you're counting unique numbers it's much easier to do using the frequency function then if you're counting unique words or text entries now let's look at another example related to count unique numbers what if we want to count unique numbers with criteria from the second column so here's an example I'm going to make this small so we can see this whole data set here saw that label there we own account unique numbers but only the ones from Oakland so that means we want to count unique numbers but we have to isolate not all the numbers in this column just the ones that are associated with Oakland now here it is we have location some site where we took a measurement for statistics and then some measurement we're not interested in all of the Oakland ones just the unique one see Oakland gets a 5 twice so in essence we're going to use the same sort of formula because ultimately we're counting these numbers but we need to add one extra if in there that says if anything in this column is Oakland 1 2 3 then go and look at the numbers in this column so you're ready we use the same construct equals some product double negative open parentheses frequency and now instead of just Highline Boop here we have to do an if because data array needs just the numbers from Oakland so we say if anything in this column right here is equal to Oakland then what do we want this that will give us our numbers from this column here we do not need to put a value of false if we highlight this if that's doing a delivery to the data array f9 we can see we got a 501 of 5 now those are the data items to count there's two fives so we control Z we do comment and the bins are going to be the exact same numbers remember even if it runs into a three it's going to the as a bin the count will be zero for that so I'm going to close parentheses on the frequency and now let's just see what we get f9 we get two for a five and one for this item right here and then yeah because we got two 5s in a single one I'm going to control Z don't want to leave that hard code in there we have to say greater than zero that will give us our throughs we close parentheses on that you see the green one right there close parentheses on that and then control enter now why let me do that again enter why did we get a value our value error means your array formula was not entered with ctrl shift enter but I thought some product would allow arrays yes that's true but there's something overriding that aspect of the sum-product function the if function right here logical test in the hierarchy of how this calculates since this logical test it is usually expecting a single true or false we gave it an array forget it no matter if this is wherever this is whether it's in a sum product or by itself in some other function you have to use some control shift enter so watch is control shift enter so the question becomes if we're making this as robust as possible do we really want to leave some product here because most people look at it and go oh that exact can do an array so I'm not going to leave some product there I'm going to take it out and I'm going to use the sum function that way if someone that sees it that knows how to do array they control shift enter it'll calculate and you don't get confusion don't say oh why isn't some product calculated it's because of that if they're all right well we get a correct count if we change this to Seattle we get a correct count also even though there's another Seattle there there's only one unique item to come when we get it - all right so numbers we did a unit count on straight numbers and now we did a unique count of numbers with criteria for a second column we want to go on and look at a formula for counting unique anything not just a number now here's a well-known formula for counting unique anything and we're going to start by counting those numbers back over there equals some product and our Ray is going to be one divided by and they're going to use the count if function we're going to compare and contrast the countif function to the frequency and to the match which we're going to use later on in these two argument of use similar trick I'm going to put all the data items here and then before we did data array and bins here we have range and criterion they're going to put all of the items for the criteria so I'm going to go over and get these numbers control shift down arrow f4 f4 to copy this now there's a couple important things about these arguments I'm going to comma control V close parentheses now the range in count if can never be an array criteria because it is expecting a single criteria like a number or word or something we're giving it a bunch this argument can handle an array now well that's going to cause a problem later on to our knowledge that this can never handle an array is helpful but let's see what this countif does compared to the frequency remember that we have all the items and all the items so if I highlight this and hit the f9 key you can see unlike the frequency that went one and three remember that invoice was listed three times when it was subsequently seen at the duplicate bin there was a zero and then another zero so when we could just say something like greater than zero but here look what it does the first time it sees the invoice it says how many of them are are there three the next time it sees it it says how many are there three so really we're going to have three three three for that one invoice but what we do is we say 1/3 plus 1/3 plus 1/3 well what's 1/3 three times it's one so this is a great way to get a unique count control-z and you can see if I highlight that whole thing and hit the f9 it's hard to see up here but we get instead of the threes we get point 3 3 3 3 3 which is 1/3 now I'm going to control Z how come there's so many threes there because there's multiple items over there listed three times now I'm going to close parentheses and enter notice it gives us the right count now there's a potential problem with this method if there are never any duplicates or wildcard characters like asterisks then this formula could work but it's not robust because there's lots of things there's a few things that could give it a cause of trouble if I come over here and hit delete we can see our frequency formula work just fine but now what's happening here we get a divide by zero error and the reason why is simple if we highlight this count if and hit the f9 just to there and hit the f9 you can see there's a zero there so one divided by zero is divided by zero and you're not allowed to do that I'm going to control Z control Z that was just getting rid of that undo all right so that that function has limited use if you have blanks or wildcards let's go ahead and see an alternative very similar to this but we'll just add one other array that will eliminate the blank so I'm going to say equals some product and I'm going to use double negative open parenthesis I'm and I'm going to choose a different range here this range right here and I'm going to say not less than greater than double quote so that means not blank that will give us a bunch of ones and zeros , and then we'll use our 1 / count oh actually we don't need to do well let me put the 1 / count if and show you something interesting here and we're going to list that range ctrl V comma ctrl V now we still will get it divided by 0 and I'll show you if we go like this f9 we get a divide by 0 which will mess everything up so what's this ctrl Z we can this argument inside a count if can handle a race but this one cannot so no problem we can come to the end here and go ampersand double quote that says please add a blank bit of text to whatever the elements are here and let's just take a look here if I hit f9 you'll be able to see that the number 12 and 0 are numbers they're not in double quotes double quotes means it's text ctrl Z but if you highlight this whole thing here and hit f9 you'll see that oh the 12 and the 0 is not there anymore it's just double quotes so it's actually text and that's a clever way of getting rid of that 0 1 divided one divided by this one divided by this text will not give us a divide by 0 so I'm going to ctrl Z now let's close parentheses and I'm going to hit enter now notice we didn't get a value error this criteria argument and the count if we put an array there it doesn't mess up the sum-product but the if right here forget it not logical test is programmed in such a way that the sum-product can't handle it so we went ahead and put some there now look at this we can actually make this a little bit more condensed because we have two arrays right these are ones and zeros and then this is on the second array where we divide by one but check this out anytime you do an operation if we get rid of that right there we're left with trues and falses now unless we do an operation like multiplying or double negative or adding zero or divided by one or something like that or times when these are going to say true and false and some product can't understand it but control Z if we leave that there we can just have true and false is in the numerator and now get rid of that we have one array so we have trues and falses because they're going to be divided by these items here these count these items that we counted it will convert the trues and falses to ones and zeros let's just enter and we can see we get a count of four let's go ahead and alt top this formula ribbon formula auditing evaluate formula which is off the screen right there I'm going to use the keyboard shortcut alt T u F alt tough and then you can see here evaluate evaluate evaluate it's going in here it gets the count now it's going to divide this by this and wherever it's a false divided by number it's just going to give us a zero so one more time and then we could see we get our point five point five point five point five it adds them together it gets two or four sorry for because there's four unique items and there are four unique items now let's go ahead and look at what happens if you like to put asterisks and your intention is you want this to be counted as different than this when I hit enter it gives me four point five yeah that's just not going to work now let's look at a potential solution for this there is a character the tilde character tilde is to the left of the number one that can deactivate wildcards let's come up here and if we were allowed to do this we could come to the range argument and simply for the items we're looking at we could ampere sum in put a tilde in double quotes a double quote tilde double quote and double quote ampersand now that would do it if we could do it if we try to hit answer here it gives us this message this message says a bunch of stuff but what it should say is the range argument in count if never can handle an array so please don't try to put one there I'm going to click escape I'm going to click escape well before I hit escape since we realize that that's not possible there's another way of counting unique there is we'll use we'll switch to the match function which will give us an ordinal position and we'll use it inside our frequency the match function will be able to handle this little tilde here we're going to have the same construct inside of Matt we're going to have in essence our lookup values with the tilde and then our lookup range will have this ampersand to deal with the divide by zero so let's see how to do this I'm going to get rid of this right here now our goal here is in this formula count unique anything if they're blanks and/or wildcards now this formula here is going to be the basis for the rest of our tricks here and it's going to involve a bunch of little tricks one of the things is we're going to use the if function again and inside the if we're going to have an array so we're not going to use some product to add we're going to just like we did over here right we didn't use some products as we knew we had to do control shift enter to get those to enter the format because of the if so I'm going to do the same thing here that's the beginning part of this I'm going to scroll over here and I'm going to switch datasets and I'm actually going to scrunch this up so I can see everything here's our data including that asterisks now I'm going to start off with some and then an if now our goal with this formulas to put some get trues and falses in this argument from here at rooming unique unique unique unique not unique not unique and that's not unique either that's not unique right if we could do that then we type a comma and the value of true would be 1 that way the throughs and falses will give us a bunch one's and then the sum function would add it now we don't need that value of false because if we just leave it out like this some will ignore it falses would be put in and the sum function can ignore that so I'm going to get rid of that the whole heart of this formula comes down to how in the world do we get our logical test a string of trues and falses to tell us unique item one other thing important about this argument here this argument can have a true/false or a string of trues and falses but it can also interpret any nonzero number as true so that means negative 10 10 3 1 are all true 0 is the only number that's false all right and we're going to deliver some numbers zeroes and numbers just like we saw earlier when we were counting unique numbers remember the frequency delivered a number anytime there was a unique entry and a zero anytime it wasn't alright so we're going to have to do some tricky things here because these aren't just numbers but we have to start with frequency so I'm going to start with frequencies data array we first have to eliminate the blank so I'm going to say equals if anything here is not double quote so less than greater than double quote that's not blank then what do we want now we can't use the count if here so we're going to switch to match match will give us the ordinal position and maytee tab the ordinal position so we're going to get one two three we don't want anything there for nothing five so those are ordinal positions we want so we're going to do similar to with account if lookup value and lookup array but here the lookup value we can go double quote tilde double quote ampersand this range right here control V because I have it copied that's the look of value comma lookup array control V and we don't want that divided by zero so we're going to ampersand double quotes now very important when we get to the match type here we want to put a zero for exact match and the reason why is when we put a zero for exact match it will only find the first item so for example match will give an ordinal position of 1 for su but the next time it finds su because it's returning the match will return an array of numbers it's going to also return a number 1 so that's what the zero does duplicates always only look at the first one close parenthesis and let's just see that that's true I'm going to highlight this match and f9 should oh if we get a 1 2 3 4 5 1 right because there's a duplicate right there but did it see the Jo 1 2 3 4 5 it did it saw it as a unique item but also notice that blank right there but no problem this will eliminate that number ctrl Z for the blank so if not blank then that that is our value if true we close parentheses we do not need the false and we can go ahead and highlight this this will be our data array f9 notice we get a 1 2 3 5 1 7 so there's two ones this is in the frequency that so that's data to count the bins when we give it 1 the first bin the number 1 the upper limit we'll get a 1/2 because there to them this array right here is just the data control Z control Z comma now what in the world do we do bins for bins well notice that we did match was given us gives us an ordinal position so we really need as the bins 1 2 3 4 5 etc I'm going to do our old stand by the row of that oops the row of that now what will that that will give us five to twelve we don't want that so we - row of that first cell right there that first cell right there so 5 minus 5 is 0 so we don't want that so we add one back in close parenthesis this row will give us f9 our bins I'm going to control Z this will give our items to count which we already saw had two ones so that frequency we can close this off when we close it off right there so now if we highlight this frequency right there f9 remember it turns over a vertical array sooo there's two of them 12 one two one ah there's a blank nothing that was from the if blank the one that ones there oh there's a zero right there because member frequency ignores when we have a bin duplicate and then the one for there I'm going to control Z just take a visual look here this logical test remember any num nonzero number is true so this is going to be true true true false true false true etc control Z and now the rest of it's easy we already looked at it because logical test comma what do we want if true one now we don't need the false so I'm going to put closed parenthesis I don't need a closed parenthesis on the sum so close parenthesis and control shift enter five totally amazing all those little tricks in one formula now that is the most robust unique count formula that I have the seen around but now we don't want to just count a single column we want to look at some variations on this we want to do a couple things one is we want to actually count unique names here but only but based on criteria and a second column so imagine this we have the criterion oh and we want a unique list of names well this is a voting situation the column of names you can vote as many times as you want the vote over here so we just want to see how many unique people voted for no you can see how I've conditional for mates no is listed 1 2 3 times if there's a duplicate on sooo so sure we should get a 2 here we want to change it to yes we should get 4 all right so we already did the heart of this formula the only difference between what we just did and here is we're going to have to isolate this column instead of the whole column we want just the ones that are in yellow all this means we're going to use the same formula here because this is the most robust type of count unique formula that I've seen we just need one more criteria so let's do a let's do this I don't want to retype this out I'm going to do a big cheat here I'm going to copy this and I'm going to paste now watch this is going to be so weird when I control when I put it into edit mode let me just take a look now it copied over it looked like it got it exactly right remember over here this formula we just had a single column so all the ranges in it we're looking here same thing here everything's looking there I set it up so that when we copied it over would do that and everything's perfect except for what one thing we're getting a count of six and in fact there are unique names there's one two three four there's a unique name and there's a youth name so there's six but remember if we have criteria in this column that means we need to use the yeses to isolate not everything in this column just the yellows no problem we just come right here the first if was please no blanks we just need another if right here if is ctrl Z let me scoot this over here we just need another if so I have to very very carefully find root there that's the value of two I need another if if anything in this column is equal to this then the match so really now we're saying not blank in this column also there has to be a true from this column when it says yes and then do that same match the match is exactly same but don't forget we've added an extra if so we have to come right here and put on one more parenthesis for that if right there control shift enter now let's try it let's do a no and there we go let's put a blank now we have a two right there even though there was no here it brought of reason there was a blank there and that works control Z pretty cool now we're going to take this one step further here we're counting unique items in this column with our most unique robust formula based on a criteria here we want to do the exact same thing here but instead of just counting want to extract them all right this is probably the most wild formula we're ever going to do here but let's think about this we can kind of simplify it a little bit we've done the heavy lifting here we have inside of this if this frequency remember right to there I think I got right if I hit f9 remember that was our true/false trigger to tell us what what items to count I'm going to control Z I'm going to copy this I'm actually going to use the same thing over here inside of a extract records formula alright make this really small hair equals if now when we copy this down right now we only need to extract soon Sue's name and lungs so we need a trigger I want to get past - we need to turn it off so we just go hey rose we're sitting in a k2 so I'm going to put a K dollar sign to go on a k2 that's our number increment or as we go down this will count 1 2 3 4 I'm going to say when that is greater than this and now I'm going to hit my f4 key to lock it if that's the case , what do I want I want to show a blank right down here I want to show blank otherwise what am I going to need I need a lookup function and I'm extracting multiple items so I'm going to use index the array of items to extract our here I'm going to hit the f4 key to lock it comma and now I've done many other videos on this we need multiple row numbers now we need to in this cell but want to copy down then we need one two three four five we need six so we're going to use our small which can successively extract our row numbers the array we're going to use our same construction we've seen in other videos if some criteria is true then please give me the row number here so we're going to see if and our logical test I'm going to control V that whole thing over there the same exact thing we just did now the only problem with what we did over there is we didn't lock every anything so I'm going to have to go here and do it manually I'm hitting the f4 key everything needs to be locked all right so that's our logical test if that's true which means the it this frequency is going to deliver all that what do we want we want to row number what we already have the row number thing figured out here right there so I'm going to copy and paste now I'm going to close parentheses we do not need that false I'm going to that's the array that is that amazing look at this whole thing right here if I hit this and hit the f9 key we get a two a bunch of false as a six and that's exactly what we wanted so that whole thing is sitting inside a small and then we need our number increment or as we go down go and take out the first smallest and the second small so control Z and I go throws copy okay I'm going to put a comma control V close parenthesis on the small that's our row number so we close parenthesis on that we already had that is our false so we close parenthesis on that control shift enter and then copy it down all right now let's test it let's do yes let's also put a yes right hearing a little and then we get an sfd also there now I'm going to control Z right so we've done something very wild here like that luckily we did it in successive pieces and pasted it all together so that is extracting unique records from one column with criteria based on another the final frequency example we're going to look at here is what to do if you want to extract items but place them horizontal in addition the other complication is we have lots of items over here there was just one list over here we have lots of Lists so as we copy down we want to extract new unique list so we're not going to be able to use absolute cell references because if our formula here is looking at this range it needs to be only locked going this way but when it moves down to the next row it needs to be looking at the next range of values the second point is also we have items listed and we are going to use the frequency function to tell us which ones are unique but the problem with frequency is it delivers a vertical array so if we have the frequency function inside the if delivering a vertical array and we want to deliver column numbers which is what we're going to need because we're going to copy these across the columns we're going to have to use the transpose function in either the column part or the frequency part to orientate both rays as columns all right I'm going to do the whole one here from scratch this is going to be wild indeed now I've already done the count unique I just counted each one of these so each row has a new unique count and since they're just numbers you just get to use that part right to ready equals if and then we can't use remember this is extracting records we're ultimately going to use the index function to extract records but we do need to as it copies this way show blanks when it gets past three so we're instead of the row so we're going to have to use columns I mean a oh so I do dollar sign because we're copying across the column a Oh : a oh I didn't do that right oh no a oh that's why it's hard to type in oh I typed in a zero : a Oh whenever that is greater than Oh columns did I put I need an oh there that shouldn't look like that and I can't see the arrow when you do a video this long sometimes it's let's see if I can do it this way I'm going to click there and then come down 100 to see I couldn't see that and then I'm going to control period or shift : and then I'm going to lock just this one f4 just in front of the column alright whenever that is greater than this this also needs to be locked on the column because when you go this way it needs to be looking at three but when it moves down it needs to move to that one whenever that's true then we want to blank otherwise we're going to use index index needs the array of items to extract it's this and you better f4 three times to get it locked on the column but not when it goes down comma the row number now we need column numbers here but if there are only columns in our array right there we don't need to put a comma and get to the columns number argument we can just put it in the row arguments now we're going to have to use small and when you have to use if and we're going to have to use a frequency function here data array and we need to eliminate the blanks and then use match to get the ordinal position so I'm going to say if this thing right here and I'm going to lock it across the columns is not blank then I want match I'm going to use this range right here locked with the columns a lot so I copied it we're going to make this as robust as possible so we're going to put double quote tilde double quote ampersand that's to eliminate if there were any asterisks ctrl V comma and then ctrl V on the lookup array ampersand double quote in case there's any blanks comma don't forget the match that match of 0 for exact match says if there are duplicates it will not go on to the second one close parentheses on the match that is all we need for the value if true we don't need to evaluate false so close parentheses that gives us our data array our bins we're going to have to use not Rose we have columns so we're going to use the column function control V on that range minus column of that one right there ctrl V and then backspace let's see it and get this out of the way notice all those columns are locked because when we're going this way it needs to be locked but not when it goes down close parenthesis plus 1 that's the those are the bin arrays close parenthesis now let's go ahead and highlight this frequency and hit the f9 and sure enough we get unique unique zero for not unique zero for blank unique and zero for blank so I'm going to control Z now that's the logical test now remember we're ultimately dumping a column numbers into small right so what for this small array so we have to type comma and the value of true from that frequency is this thing right here ctrl C that's the value of true and when we close parentheses on that we see that the small now has its huge it construction here if we hit f9 we can see that that is not what we want one two three now check this out this is where we're going to learn about vertical and horizontal arrays you can see comma 1 2 6 and then a semicolon 1 2 6 and then a semicolon what it did is it jumped all the column numbers every time it saw a number or a true control-z let's just look at the two pieces together if all of this that is a vertical array with semi-colons control-z this part is a f9 whoops escaped right just to there f9 that part is columns it is a horizontal array so we need to adjust one of them either this one or this one I'm going to adjust this one control-z I'm going to put transpose it'll take from columns and move it back to move it to rows close parentheses so now when I transpose on this and hit the f9 we got our semicolons which means a vertical array or rows now when it says if vertical trues and falses it'll just take this vertical in or just 5 let's go ahead and test it all the way to there and f9 sure enough you can see now 1 semicolon instead of 1 to 6 control-z Wow now we need our call our incrementer as we go across the column so I'm going to copy that control V close parenthesis on this small will just do that again just got a close parenthesis on that you know you get lost in these big functions if you don't have these screen tips popping up you're like what we just put the row number in we already did our false so we close parenthesis on that we control shift enter and if I get this right this time I'll be stunned copy it over and copy it down totally amazing so if I type a 1 here sure enough that works if I put an asterisk then sure enough that works also alright if you wash this to the and I'm amazed that was everything I know about the frequency function and the reason why I did this video is just because there are so many amazing uses and if we kind of go from the beginning to the end and look learn all the pieces as we go through which in essence I had to do before I could figure out how all this works but look at that alright see you next trick

Channel: ExcelIsFun

Views: 29,398

Keywords: Excel, 2007, 2010, FREQUENCY, Array, Function, excelisfun, count, unique, extract, records, vertical, horizontal, array, data_array, bin_array, arguments, Mike, Gel, Girvin, Highline, Community, College, functions, formula, formulas, advanced

Id: uUrI8hoj8BA

Length: 51min 49sec (3109 seconds)

Published: Wed Jun 16 2010