Help Me With Sign Minnesota Banking Word

Help me with industry sign banking minnesota word computer

welcome back your excellency in this lesson we're going to cover a grab bag of functions and techniques to help you do even more with our workbooks by extracting and manipulating data now we're going to start with our workbook as we left it after the end of our functions related to if lesson and we'll use the data in this workbook to extract a list of unique industry names for those industries that are in the fortune 500 we'll use the unique function for this we'll override any errors if excel can't retrieve the industry for a given firm we'll learn how to use the sort function to sort the data that we extract we'll learn how to write functions that work with spill ranges like our industry data that spills out down this column we'll use wild card characters to search for partial words or phrases that might occur inside of the cells that we're searching and we'll learn how to create a drop-down list from a spill range so let's buckle up and be prepared to extract some more big learning so again we're going to be starting with the workbook that we finished at the end of our video lesson functions related if so make sure you've got that workbook open and within this workbook we're going to be working with the worksheet table rev500 but we're going to modify this data so let's duplicate this worksheet and rename it right click on table rep 500 select move or copy click move to end check create a copy and press ok and we see that we've got a copy of table rev 500 at the end of our list of tabs let's double click the name of that worksheet in the tab we'll rename it as industry press return and then make sure that you click that tab click anywhere inside the table in that worksheet and we'll clear the filters on this table by clicking in the data ribbon and selecting clear now we also don't want a total row so in the table ribbon i'm going to deselect total row and now let's use excel's stocks feature to add a new column that has the industry name for each firm so let's click on this icon in the upper right hand corner of our table and we'll select industry now an industry column is added we'll resize this column and we see the industry categorization for each firm in the fortune 500 but we've got a bit of a problem if we scroll down through this data we see that some firms can't be classified by excel stocks data and when that happens we get a number sign field error now i'd rather not show that error in my data it's not very clear to the user i'd rather have the word unknown show up in the industry for any firm where we don't have its industry well we can actually make that happen using excel's if error function and i'll create my new error corrected industry column in column g so i'll click in g1 i'll give this column the name industry name and we see that excel considers this to be part of the table since the formatting is already extended below and we'll enter the error correcting if error formula starting in g2 now you can find the iferror function in the formulas ribbon under logical but remember if you can't find iferror you can always just type it in and if you press fx in the formula bar you'll bring up the formula builder which offers you some helpful information down below and it shows you the parameters list now this function is super basic the value parameter is the address of the cell that we're going to look at if there's no error in that cell then we'll just show whatever value is already in that cell and so to get that value i'm just going to click over in cell f2 and make sure you click in f2 you don't want to click the industry column you want to click right in cell f2 the at sign industry in between the square brackets is just excel's way of saying hey take the formula that's in this cell and put it in every single cell that's inside the industry column but if there is an error well we use whatever we put in the second value value if error parameter now this could be a formula a number sometimes we'll want to put zeros in there for any values that don't contain a number but where every other value in the column has a number but for the field error that shows when we don't have a value for industry we'll just enter the text unknown so that's what i'm typing in here if i forget double quotes around this text excel will add those quotes for me when i tab out of the field or press done so now i'll press done and since this is a table excel automatically puts the function in all cells in the new industry name column and when we scroll down will you look at that every place where we have a field error in the industry column we now have the word unknown in our industry name column nice so i don't want the user to see the column with the errors in it so i'm going to right click and column f4 and select hide and now i want to get a list of the individual industry names that are listed in column g now i don't want them to be repeated i just want to see one name for each industry that exists in g and we can get this by using the excel function named unique now i'm eventually going to want to put some stuff above this list so i'm going to add my list of industries starting in i5 that's where i'll put my label so i'll type the label industries in here then i'll use the mac shortcut to bold and underline this command b command u on windows it's ctrl b control u and then in i6 this is where i'm going to use my unique function so i'm going to type equal sign unique then i'll press tab to select this from excel's formula completion and i'll click on the fx icon in the formula bar and you'll see that unique has several different parameters now we're only going to use the first parameter and that's what you'll use most of the time when you use this function the second parameter is used if you want to search data horizontally in a row instead of vertically in a column the third parameter is used if you only want to return values that show up once and are non-repeated but we don't want that option either so i'm going to click on the array field and this time i want to select the entire column not just the cell so i'm going to move my cursor into the industry name column and i'm going to click when i get this thick downward pointing arrow and we see that the table reference is entered in the array field now yours likely isn't table 14 it might say table 3 it'll just use whatever name excel gives your table by default if it's an unnamed table and then i'll click on the done button and will you look at that if we scroll down we can see the list of individual industry names that we extracted from the industry name column very handy now the data below i6 is actually the spill range there's no formula in those cells the results simply spill down into those cells based on the formula that's entered in cell i6 and the spill range will take up all the cells that it needs as long as there's not already data in those cells now we've already seen spill ranges before in our previous lecture when we work with x lookup and just like before we see that the spill range is highlighted with a dark blue outline well that's cool but now that we have a list of industry names can we get a count on the number of firms in each industry well i bet you know how to do that and we'll go ahead and do this together based on what we know but then i'll also show you an additional technique that will show the proper way to deal with formulas that address spill ranges so why don't we start by adding the label number of firms in j5 and i'll bold and underline this then in j6 how would we get a count of the number of firms in a given industry well we could use countif that's a function we introduced in our last video lesson so in j6 we'll enter equal sign countif and i'll select countif and formula completion using the tab key and then i'll click on the fx icon in the formula bar and we'll open up the formula builder and for the range that we want to count up it would be the column under industry name so i'm going to move my cursor so that it turns into a downward pointing arrow above industry name and i'll click to enter the reference and since we're using a table reference this is going to act like an absolute reference so we don't have to worry about the table reference shifting if we autofill this formula to other cells and then for the criteria field i'm going to click in i6 that's the first industry in the list of industries that are unique function generated and then we'll click on the done button and i can see there are 14 fortune 500 firms in the food and drug retailing industry now remember we've got a spill range below i6 so even if we click on i7 we see that we don't have any formula but we can still reference these spill values in other formulas watch this i'm going to double click in the auto fill handle in j7 and hey my formula fills down for as many cells as i've got in the spill range just to what's left and i correctly calculate the number of firms in each industry using countif very cool but here's one issue if we change the original table data which we use to get the unique industry names starting in i6 then those unique values will change now if we change the table results by removing industries we'll have formulas under j6 that include extra cells since we now have fewer cells in column i after the deletion of industries now this will be much clearer after we work through an example and then i'll show you the proper way to create a formula that refers to a spill range so first to show you what happens when i delete all of the rows in an industry i'm going to click on the filter triangle in industry name and i'm going to sort in ascending order and notice that the unique values are also sorted since unique simply fills in the values based on the order it discovers them in a range and we've just sorted that range and then i'm going to delete all of the firms in the first two industries aerospace and defense and automobiles and auto parts so the way that i'm going to do that is i'm going to click starting in row 25 of the industry name column i'm going to hold down my mouse and i'm going to drag up to highlight through row 2 of the rank column and then with all of these firms selected i'm going to right click on my selection i'm going to select delete table rows and i've deleted all those firms and i also no longer have either the aerospace and defense or the automobiles in auto parts industries in the column list in column i now here's the problem with the way that we entered a countif function we auto filled in j6 based on the values that we're spilling from i6 but we just deleted two industries so we no longer have the same number of industries in the spill range and when we scroll to the bottom of our industry list we see that we've got two zero values listed to the right of blank cells at the bottom of the industry names so although the industry spill range is smaller our formulas were copied to all of the cells in the larger spill range through j51 and we now have two extra formulas in j50 and j51 because the spill range has shrunken by two cells but fear not starting with excel 365 microsoft has given us a special spill range operator and it's really easy to use but before we use it i'm going to undo the deletion of the aerospace and defense and the automobile and auto parts firms i want those guys back so do a command z mac ctrl z windows to undo and i'm also going to resort the table based on its original rank order and the way that i'm going to do that is i'll click on the filter triangle right underneath the rank header and i'll select ascending order now i'm eventually going to show you a sort function but for now i want things to look exactly like they were before we deleted those two industries and now before i make any changes in the formula in j6 i'm going to delete all of the formulas that i had previously auto filled down below j6 so i'm going to click on the range from j7 through j51 and then i'm going to right click on that selection and then the context sensitive menu i'm going to select clear contents and i actually won't need any of these formulas that i just deleted because i'm going to use our special spill operator so i'm going to head back to j6 and you can see our formula here where we had our criteria field in our countif that was just i6 well let's use the spill operator all we need to do is add a number sign after i6 that good old hashtag that number sign is the spill operator and that my friends will tell excel to look at all of the values that spill in the range starting with i6 and apply this formula to all the cells that show up in the spill range and here you can see excel helps us out by trying to show us what's going to be happening so i6 number sign shows up as red in the formula bar and we also see the spill range highlighted in red on our worksheet that's it press return no need to even autofill because there are no formulas in the cells below j6 they're just automatically applied to the spill range because we use the number side spill range operator very nice now i'd mentioned before that will also show the sort function and that'll allow us to keep our table sorted in rank order but apply an alphabetical sort to the results that spill out starting in i6 now the sort function has a bunch of optional parameters so it can get pretty complex there's also a sort by function but for doing a simple alphabetical or smallest to largest sort the sort function is really easy just pass in any range that you want to sort and that's it so what i'm going to do is i'm going to click an i5 and i'm going to highlight and then cut out my entire working unique function i'm not going to highlight the equal sign i'll leave that in there but i'll cut out everything else that's command x mac control x windows and that whole expression that i just cut out is the data that i want to sort so i'll just wrap that inside of my sort function and then i'll simply type in after the equal sign sort and i can tab accept this from formula completion and we'll click on fx to take a look at things in the formula builder and we see a bunch of other parameters in there but we're going to ignore all of this complexity for now the only field that we need is the array field this is where we refer to any data that we want to sort in alphabetical or smallest to largest order and this is where i'm going to paste in our working unique function command v to paste it in control v if you're on windows then click done and when you look at that industry is all sorted now i mentioned previously that i want to enter a few things above our industry list so let's start by entering a field where you can type in an industry and see the count of the firms just to its right and we know how to do this it uses techniques we've already covered in a prior lesson but it'll also give us a chance to demonstrate something we haven't seen in our lessons before wild card characters so first i'm going to enter the label industry in i1 then count affirms in j1 i'll bold and underline both of those guys and how about if you want to challenge yourself why don't you try to enter a formula in j2 that will give a count of the number of fortune 500 firms represented by the industry that you enter in i2 so if you want to try that out once you pause give it a shot and here's a demonstration of the solution in j2 we'll just type in equal sign count if i'm going to tab to accept this in excel formula completion then i'm going to click on the fx icon to open the formula builder the range is simply the industry name column in my table view and the criteria is i2 and that's it now when we click on done we see that there's a zero in here for starters because we've got nothing inside of i2 but why don't we type in an industry like say beverages and we can see that there are five firms in the fortune 500 that are classified as being in the beverages industry now here's where wild card characters come in not only can we search for an exact industry match we can also search for all industries that contain a subset of characters that i type in for example if i scroll down in the industries list we can see two industries that begin with the word healthcare healthcare equipment and supplies with 13 firms and healthcare pro iders and services with 15 firms that's 28 firms altogether well if i want to search for all of the industries that begin with the word healthcare i can simply type in healthcare followed by an asterisk now the asterisk is a wild card character and it means in a search i can ignore any characters or even no characters but i'll consider any phrase a match that starts with forward healthcare so when i press tab to go to the next field i can see that the number of firms that begin with health is 28 just as i'd expected now wild cards can work in the front of a phrase as well so for example we've got two industries down here with the word utilities at the end multi-line utilities and natural gas utilities and together they make up six firms in the fortune 500 so if we head back to i2 and enter asterisk utilities we see a count of six firms now we can even use an asterisk as a wildcard character at both ends of a phrase that would ignore any values that become before or after a search term so for example we might want to do that with the word gas because there are three categories that contain the word gas there's natural gas utilities oil and gas and oil and gas related equipment and services and collectively they make up 34 fortune 500 firms and to count them up we can just enter star gas star and we'll find any industry that has gas anywhere inside of it and we see as expected we get 34 firms now the asterix or sometimes called star character is a wild card character that you'll most often use it represents any number of characters including no characters but there's another wild card character the question mark which represents just a single character also if you ever need to search for a wild card character for example if you need to find asterisks or question mark which frankly i've never needed to do the tilde character will let you do that now using wildcard characters in search is actually pretty common in many computer programs and most follow the convention of star or asterix representing any number of characters in a question mark representing a single wild card character so these are good to know even outside of excel and now for our final act in this video lesson let's create a drop-down list showing all of the industries in the fortune 500. now let's do that in i3 and we already know how to create a drop-down list but there's one catch we need to pay attention to if we're creating a drop down list from the results that come from a spill range so first let's use what we know we'll create a drop down list by heading to the data ribbon and clicking on data validation and then in the settings tab under allow we'll select list now under source we know that we can enter the address or range for the data that we want to use in our list so why don't we try clicking on i6 now excel turns this into an absolute reference with dollar signs that's fine but when we click ok we see that excel only used the single value in i6 to create our drop down list hmm how can we fix this spill operator to the rescue so with i6 still selected let's click on data validation again and let's simply add a number sign after the absolute reference to i6 that's it that hashtag is our spill range operator it says use all of the values that might show up in the spill range click ok check our drop down and this looks magnificent we see all of our industries in the list very nice want to get a firm count based on what we select in that list why don't we copy the formula from j2 we'll paste it in j3 then why don't we select an industry how about beverages and we see the count shows up as five and with that your excellency i think you deserve an age-appropriate beverage of your choice because you've just conquered another set of excel skills we've learned to use the iferror function the unique function we learned to work with spill ranges we used the spilled range operator we used the sort function we searched with wild card characters and we created a drop down list from a spilled range now i hope you found this excel tutorial to be useful and i continue to wish you all things excellent keep at it