Excel Formulas and Functions | Full Course

Excel Formulas and Functions | Full Course
Spread the love

hi everyone kevin here today we are    going to learn about formulas and    functions in microsoft excel    maybe you have a job interview coming up    or a test where you have to demonstrate    your knowledge of excel    don’t worry i’ve got your back    we’re going to start with the    fundamentals of how you can even enter a    formula into excel    and then we’ll advance to the most    commonly used functions    by the end of this video you’ll be a pro    with    formulas and functions    to follow along i’ve included a workbook    down below in the description you can    also jump around this video using the    timestamps down below    alright let’s get started here i am now    in microsoft excel and i’m currently on    the formula fundamentals worksheet just    in case you want to follow along    and i want to calculate how much revenue    i earned from the three different types    of cookies that we sell here at the    kevin cookie company so i could use a    formula to calculate this but even    easier i want to add up these three    different cells when i highlight these    three different cells    if we look down below here on the status    bar you actually see that we have the    sum right here it’s 23 and that’s how    much revenue we made from these cookies    so you don’t even have to enter in a    formula to get some basic metrics back    and in fact if i click on the sum here    i can go into the total cell and press    ctrl v to paste and here i’ve added up    all of these different cells just like    that just by using the status bar    now down below when i have these three    values highlighted i could also go to    the status bar and right click    and here if i scroll down this menu    you’ll see that i also get the average    the count here i can get the min the max    and here i have the sum so here if i    check this on here i can also see the    max now down below on the status bar so    this is one way to get some quick    answers without even needing to enter in    a formula    now of course this video is all about    formulas and functions and we should    probably enter in our first formula    so here i’ll come up and let me delete    the 23 that we got from the status bar    and here once again i want to add up 10    plus 8 plus 5. so the first thing i need    to do to enter a formula is to enter the    equal sign    this tells excel that i’m about to enter    in a formula and here i could take 10    and i could add 8 and i could add 5.    here you could see my very basic formula    now if we look up above here in the    formula bar you’ll see the formula here    as well    to calculate this formula now all i need    to do is to press the enter key    congratulations    you just entered your first formula in    excel    to go back and edit this formula here i    can click into this cell and here we see    the formula up above so i could modify    the values up here    or alternatively i could also double    click into this cell and this will once    again show me the formula and then i can    make modifications here    now one problem with just entering in    the formula like this let’s say that now    we made revenue of 11 on our chocolate    chip cookie here i’ll press enter    you’ll see that this did not update and    the reason why is we hard coded in these    values so as these values over here    change my formula doesn’t change so    instead of hard coding these numbers or    values in instead i want to reference    the different cells    so here for example instead of typing in    10 i want to use cell b2    so here let me delete 10 and i can type    in b and then i’ll enter 2.    and here you see that the cell has now    highlighted    and here also for the eight instead of    hard coding that here i’m going to type    in c2    and also here i want to type in d2 for    the sugar cookie revenue    and now i can press enter and here you    see i get the same results but the nice    thing now is let’s say the revenue for    the chocolate chip cookie changes let’s    say it’s now 11 you’ll notice that the    total automatically updates    when i click back into the total cell    here you’ll see that excel uses    different colors so here you’ll see blue    on b2 and this is also highlighted by    blue here c2 is red and here you see red    and here’s purple and here you see    purple    so the colors here in the formula    correspond to the cells that they’re    referring to    now of course typing in a cell reference    works but it also requires quite a bit    of effort    and instead we could use our keyboard or    our mouse to reference specific cells    here once again in the total cell i will    enter the equal sign and instead of    typing in b2 i can simply use the arrows    on my keyboard to select that cell    here i’ll type in the plus sign and i    can arrow over and once again i can    arrow over and then hit enter so that’s    yet another way i can refer to specific    cells    but even with the arrow keys let’s say i    have a very large sheet and it can take    a while to get to the cell that you care    about here once again i’ll click on    delete    here let me enter the equal sign and    instead of using my keyboard i can also    use my mouse here i can click on b2    and i can press the plus sign and here    let me select c2 plus and then d2 and    here too i could press enter and once    again we had revenue of 23.    now that we know what the revenue is    let’s say i want to know what the cost    is now once again i could type in the    equal sign and i could select this cell    i could add this and then i could add    this so just like we did up above but    that takes a bit of effort    instead i can click into this cell where    we already have a formula and i can    click on this fill handle in the bottom    right hand corner there you see that my    cursor changes to a plus icon and i can    simply click on that and then drag it    down    and here you’ll see now that the formula    has updated where it adds up all of my    different costs    so the formula adjusted relatively    i moved the formula down by one row so    it also updated all the references by    one row    over here i can also use subtraction so    let’s say i want to calculate the profit    to calculate the profit that’s the    revenue minus the cost once again i’ll    enter the equal sign and here we had    revenue of 10 for chocolate chip and let    me subtract four and once again i could    press enter and here the profit is six    now just like we did before if i want    the profit for all of these other    columns once again i can click on the    fill handle and i can drag this over and    once again it automatically adjusts the    formula relatively so i moved it over    one so the formula references moved over    1 and here you can see how that worked    for every single column that’s a quick    way to reproduce a formula in many    different columns or many different rows    now this is some pretty nice profit but    unfortunately you have to give up some    of that profit towards taxes and to    calculate the tax we’re going to use    multiplication    down here in cell b5 i want the profit    of 6 and i’m going to multiply it by the    tax rate of 10    so once again let’s enter the equal sign    and here i’ll reference this cell with a    profit and once again i want to multiply    it by 10 percent now you might think the    multiplication sign is an x but in excel    that’s just a character    to enter the multiplication sign we    enter the asterisks that’s how you    multiply something and here i’ll select    10 percent and then i can press enter    and it looks like the tax is 0.6 or 60    cents    now here once again i want to calculate    the tax for all of my different cookie    types so just like we did before we    could use the fill handle here once    again i’ll select that and let me drag    it over one column but here you see that    the tax is zero now from a business    standpoint that’s good news but i don’t    think it’s calculated correctly    here if i click on the cell    i can go to the formula bar and when i    click on this i can see all of the    different references    and here it was making relative    references so once again as i pulled the    formula over here it adjusted it to look    at this cell but if we look up here at    the tax rate it also moved this    reference over one but i want the    reference to point at this specific cell    or g2 so i basically want to lock this    reference    so let me go back to the original    formula where we calculated the tax and    in here once again i want to lock this    reference in    and we’re going to use a shortcut key to    do this we can press the f4 key or the    function four key on our keyboard and    you’ll notice that it adds a dollar sign    before the g and a dollar sign before    the two    the dollar sign tells excel that we want    to lock both the column and the row    here i’ll press enter    and now if i pull this over    you’ll notice the formula works as we    expect it to and when i click in here    you can see that it adjusts this first    cell relatively    and then it locks in the tax rate and    here it did that for all the different    tax calculations    this is what’s referred to as an    absolute reference    as a quick note here when i’m in this    cell and we see the formula up above    with the dollar signs    if i press f4 again now you just see a    dollar sign on the row if i press f4    again you just see the dollar sign on    the column so you could just lock the    column or you could just lock the row    and here if i press f4 again it removes    it from everything and once again it’s    purely just making a relative reference    but once again i want to lock it so i’ll    press the f4 key    with this formula i’m currently    referencing the tax rate that’s on the    exact same sheet but you can also    reference a cell on a different sheet so    here once again let’s go back to this    first cell here i’ll delete these    different values    and instead of referencing the tax rate    here let me delete this    and right now i need to enter in the tax    rate and it turns out there’s another    sheet called tax rate i can click on    that sheet and here we see that tax rate    of 10    i could click on it here and once again    i could press f4 to make this an    absolute reference    here i’ll press enter and once again    this works and here you see that it uses    the worksheet names so it’s pointing to    this tax rate sheet and then the cell    on that sheet you can even refer to a    completely different workbook if you    want and you’ll see the notation appear    right up here in the formula bar    now once again i can take the fill    handle and let’s calculate the tax    across all of these different columns    now this seems to work well where i’m    referencing a different sheet but when    you look at the formula itself it it’s a    little bit messy you have the sheet name    and then you have the cell reference    i’ll go back to the tax rate sheet and i    can give this cell a name so i don’t    have to refer to it as a2 but instead i    could use the name reference    right up here where you see a2 here i    can simply type in tax rate and then    press enter    i’ve now assigned a name to this cell    let’s go back to formula fundamentals    and here instead of referring to the    sheet and the cell i’m going to delete    that and instead i’m going to type in    tax rate and here you see a hint that    this is a known name    now i can press enter and that too works    and it’s a lot cleaner now now i simply    refer to tax rate and it’s really easy    to understand what that is    now let’s say that i’m entering in a    formula and i forget that i had a name    called tax rate    i can press the f3 or function three key    to see all the different names when i    press f3 here i get all the different    defined names and here i see tax rate i    can simply double click on that and that    inserts tax rate into my formula and    then i can press enter so once again    that’s another way to get back to your    different named cells    we’ve looked at addition subtraction and    multiplication and you can also do    division as well here i’ll go down to    the profit split sell    and here at the kevin cookie company we    made 14 of total profit for now i’m    going to ignore taxes and let’s say that    we have two owners of this company it’s    me and patty so here once again i’ll    type in the equal sign then i will    select the total profit that we earned    and i want to divide it by two to see    what my share is    to do division we’re going to enter the    forward slash and then here i will    simply type in two and press enter and    here we can see that my share of the    profit is seven and paddy’s share of the    profit is also seven    next let’s move on to the order of    operations worksheet    and here once again i see the revenue    the cost the profit and the tax for    chocolate chip cookies now let’s say i    want to calculate all of this in just    one cell to see what the tax is    i’ll enter the equal sign and first off    i need to take the revenue    i want to subtract the cost and that    should give me the profit and next i’m    going to multiply this by the tax rate    of 10 percent    and then i’ll hit enter but whoa what’s    going on here my taxes 9.6 but my profit    was only 6. that’s ridiculous i mean i i    know tax rates are going up but it    shouldn’t be more than my overall profit    the problem here is the order of    operations and when i click into the    cell let’s double click in we could take    a look at how excel calculates this    excel looks for multiplication and    division first and then it evaluates    that part of the formula so here for    example it’s taking 4 and then it    multiplies it by 10 percent or 0.1 and    that gives us 0.4 as the result in fact    if you want to calculate a portion of    your formula here i have this portion    highlighted i can press the f9 key and    here i can see that it evaluates to 0.4    i’ll press ctrl z to undo that    so it’s taking 10 and then it’s    subtracting 0.4 and once again that    gives me 9.6    now i wanted to first calculate 10 minus    4 and then take the result of that and    multiply it by 10 percent    to do that i can use parentheses and    that way i can define what i want the    order to be    here i’ll put a parenthesis around b2    minus b3    that way excel will evaluate this it’ll    take the result and then it’ll multiply    it by 10    now when i press enter i get 0.6 and    that’s exactly the same as what we got    on the previous sheet so one thing to be    aware of when you enter formulas there    is an order of operations    now up to this point we’ve purely been    looking at formulas and next we’re going    to look at what’s called a function    but how is a function different from a    formula    well with a function you can pass in a    parameter or an argument and then you    get a result back    now i know that probably doesn’t mean    that much so why don’t we make this real    and look at some examples    here on the basic functions worksheet i    want to answer the question how many    cookies did we sell here i see all the    different cookies we sold so i want to    add up this list now we could just use a    simple formula to do this once again i    could enter the equal sign and i could    take this and i could add it to this and    i could add it to that and i could go    through this whole list but that takes a    lot of time    instead we want to use a function and    that’ll make this entire job a lot    easier    so how do we insert a function well one    thing we could do is up here we could    click on formulas or the formulas tab    and over on the left hand side there is    the option to insert a function    as i hover over you’ll also see that the    shortcut key is shift f3 so you could    use that as well let’s click on this    this opens up the insert function dialog    and right up on top it says type a brief    description of what you want to do and    then click on go now once again i want    to add up all these values or another    way of saying that i want to sum up all    these values so let’s type in sum    then i’ll click on go and here it looks    like there’s a function called sum there    are also some other options and we’ll    get into these later but for now we just    want to sum these up    let’s click on ok    here now it asks me what numbers i want    to sum up i’ll move this over just a    little bit so we can see the numbers we    want to add up and here it says number    one number two now i’ll delete this    value and here i can click on this cell    number two i can click here but you’ll    notice that this isn’t very much faster    than just adding up all of the different    cells using formulas    so here let me delete those again    instead    here i can highlight this entire column    so here i can highlight all of these    different values and you’ll notice here    it says b2 colon b8 so i’m passing in    all of these different values basically    this range    and here you can see the range of values    that i pass in these are referred to as    the arguments or the parameters that i’m    passing into the function    and right here at the bottom you can see    what they add up to so when i sum all of    these different numbers that gives me    421 and this is a basic function next i    can click on ok and here you see that i    just summed up all of these different    cells    one thing to call out when i double    click into the cell you can see the    notation of a function so once again you    start with the equal sign that lets    excel know that you’re entering either a    formula or a function    then you type in the function name and    then you open the parentheses here you    pass in the arguments or the parameters    and then you close the parentheses at    the end so instead of using the    insert function helper you could simply    type this in    now interestingly there’s also a    function that you could enter in that    shows you a function so here i could    type in equals and let’s type in formula    text    and then here i’ll open the parentheses    and i have to reference a cell so let me    reference this cell and then close the    parentheses so i’m following this same    format that we saw with sum and when i    press enter here now we can see the    function that’s being used in this cell    so just to help you see what’s going on    to get this 421    now excel has many many many different    functions that you can use and here if    we click on formulas up on top here you    can see some of the different categories    you have financial functions you have    logical functions text functions and    there are many many different options    here    but once again if you want to search for    a function once again you can click on    insert function and then you can use the    search command    now there’s one category here called    autosum and when i click on the drop    down    this contains some of the most commonly    used functions here for example we see    sum again but you have average count    numbers max and min and then once again    you can get to all of your functions so    let’s say for example here at the bottom    of the list let’s say i want to sum it    up here i can click on formulas and then    simply click on auto sum and it’ll    default to sum    and here i can simply press enter and i    get the sum    now autosum is used so frequently that    it’s also on the home tab here i could    click on home and i could go over to    editing and here once again we see auto    sum    now when i hover over this you’ll see    once again that it defaults to sum but    here too you’ll see the shortcut key i    could press alt together with the equals    key    so here i will click into this cell and    i can press alt and equals and once    again that will also give me the sum so    that’s an even quicker way to get this    sum now once again this is one of the    most commonly used functions in excel    over on the right hand side i want to    continue answering these different    business questions and i want to know    the most number of cookies sold here    once again i can click on formulas and    let me go back to this autosum group    because it has some of the most common    functions and here’s the function for    max i could click on that    and here it enters max but it’s looking    at this cell and instead i want the max    number from this so here once again i’ll    highlight this list so this is the    argument or the parameter that i’m    feeding in and i could press enter    and here i could see that chocolate chip    had the most number of cookies sold at    97 and here we could validate that by    looking at this list i’ll delete the    421.    right over here i also want to know the    least number of cookies sold and if we    just look at this list we see that it’s    fortune with 36. i’ll click into this    cell and once again i can go to formulas    autosum and here i can select min but    instead let’s just type in the function    once again i’ll type in the equal sign    and let me type in the function name min    open the parentheses and then we have to    pass in the argument and that’s this    list of values and then i can close the    parentheses hit enter and here we see    that fortune cookie only had 36 cookies    sold    let’s say instead of knowing the max or    the min i want to know what was the    second most sold or what was the second    least sold and for that we could use the    function large or small    here i’ll type in equals and i can type    in large and then open parentheses    and here it asks for an array and a k    and if you’re not quite sure what these    mean to make it a little easier we could    click on this fx or insert function and    this once again opens up the function    arguments and here we see some hints on    what those values need to be so first    off the array is the range of data so    here i’ll click into this cell and let    me select my data or what’s referred to    as the array    and then k this refers to the number    that we want back    so here i want the second largest so let    me type in a two and here we can see    that it’s 77 for oatmeal raisin i could    click on ok and here i get the result    back so as you enter different functions    in if you need help with them remember    you can click on this fx right up above    and that will open up the function    helper and this will help you to compose    your function    down below i want the second least sold    so let me type in the equal sign and for    this i’ll type in small    and once again it wants the array that’s    this list and i want the second smallest    so once again i will type in a two hit    enter and here i can see that peanut    potter is the second least sold cookie    with the large and the small functions    we entered two separate parameters so    here once again when i open the function    helper here you see that it’s asking for    an array and it’s also asking for the    number that we want so with various    functions you may have many different    arguments that you can pass in and this    is just one example where you can pass    in two different arguments or two    different parameters    next let’s say i want to know how many    cookies had any sales at all and there’s    a function for that called count here i    could type in count and we see there are    also some other options and in a moment    we’ll get to what some of those are but    here i’ll type in counts once again open    parentheses here i can highlight this    list    then close parentheses and then hit    enter so i can see that seven different    cookies had at least some sales    now let’s take a look at the count of    the cookie names so here once again i’ll    type in count and let me highlight all    the cookie names so how many different    cookie types do we have and here i could    press enter but here it shows me zero so    why is that    well when we use the count function    this is counting numerical cells and    these cells over here have text based    values so it’s returning a zero    and once again if i click into this    let’s take a look at some of these other    count functions once again i’ll type in    equals count and here i see that there’s    an option called count a and when we    read this this will return back all the    cells in a range that aren’t empty so    let me select that and here once again i    could highlight this list close the    parentheses hit enter and here now it    tells me that there are seven different    cookie types    and just like you could count cells that    have a value in it you can also count    all of the blanks so here i could type    in count blank    and let’s say i select all the cookie    names plus one additional blank row then    i’ll close the parentheses and here it    tells me that there’s one blank value so    yet another count function that you can    use    if we move down just a little bit more    there are a few more questions that i    want to answer and this will show us    even more functions i want to know    across all of my cookies what was the    average sold to enter the average you    could probably guess it but the function    name is average and once again if we go    up to formulas and autosum here we’ll    see average here so you could also get    to it here i could select average once    again i could highlight the list and i    could hit enter so i don’t always have    to close the parentheses i can just    press enter and that too will calculate    so that saves you a little bit of time    and here i can see that on average we    sold    60.14 cookies along with your average    you can also calculate the median or    basically which number is in the middle    of this list if we just look at it we    see that it’s 52. here i could type in    median    open the parentheses let me highlight    this list and press enter and there we    see that it’s 52 and we could also use    something called the mode and this tells    us which number occurs most frequently    in this list now when we look at this    list 97 shows up once 77 shows up once    but here we have 52 that shows up twice    here i could enter equals mode    once again i’ll select this list hit    enter and here we see that 52 shows up    the most often in this list    moving on now to the conditional    functions worksheet we’re now getting a    little bit more advanced    and here we have a function called sum    if    so here we can sum up values if it meets    a certain criteria so let’s say for    instance that i want to know how many    cookies we sold    in the united states so if it’s the    united states i want to sum up all of    these values and for that we’re going to    use the sum if function    now once again probably the easiest way    to enter a function is to click on the    insert function icon and this opens up    the dialog that will help us write this    function    here i could type in sumif click on go    and here i see it down below i’ll click    on this one    here it asks me for the range and so    what is the range well here i can just    look down below and it tells me exactly    what that is it’s the range of cells    that i want to evaluate now remember i    want to evaluate whether it’s in the    united states    so here i could simply highlight this    column right here    and for my criteria i want it to be the    united states so i’ll click in this cell    now i could also type in united states    here but in this case i’ll simply click    on this cell    lastly it asks me for the sum range so    if it finds united states    in this column then i want to add up    these different cookies sold so for the    sum range i’ll select this column and    here i could already see that it’s 362.    i’ll click on ok and just to confirm i    can highlight all of these different    cells and down below i can see that the    sum is 362.    so this is how you can use a conditional    function    down below you’ll see some additional    functions like average if and here’s    count if and these work the exact same    way as some if    if some condition is met then you’ll    average out all the values or if some    condition is met we could count all of    those items so once again they work    exactly like some if except in this case    you average or you count    i want to show one neat thing that you    can do here within the function for some    if let me open up the function helper    again and for the criteria here i’m    going to type in    united states    and once again i’ll click on ok and we    see that the function works the exact    same way    but let’s say that i want to sum up all    the values for countries that end in an    s now it’s an arbitrary situation but i    want to show you how you can search    based on a wild card    so here i’ll remove most of the united    states but i’ll leave the s    and here i can insert an asterisk or the    wild card symbol so it’ll look for any    characters before the last character    which needs to be an s    here if i press enter i get a sum of    802.    so this is the sum of all the cookies    sold in the united states which ends in    an s    and also the philippines    which also ends in an s and here if i    highlight all of these cells i can see    that the sum here is 802 and that    matches up with 802 right up here so    this is yet another way that you can sum    different values based on a condition    next we have another formula called sum    if s so it looks like the same thing as    some if but why do we have an s on the    end well you can think of this as the    plural version so you can look for    multiple criteria so let’s actually test    this out to see how it works    here i’ll click on insert function and    here let’s type in sum if and s    i’ll click on go and then select this    function    and here first it asks me for the sum    range so what do i want to add up    here i’ll select the cookies sold column    and here it says the criteria range 1.    so i want to evaluate the country column    first so let’s say i want to know how    many cookies were sold in the united    states i’ll select this column and then    my criteria is united states but now i    can add additional criteria    so maybe here i want to check    was it a chocolate chip cookie so here i    could select this range and for the    criteria i will select chocolate chip    and then i can click on ok and here i    see the sum of chocolate chip cookies    sold in the united states is 28.    building on this let’s say i want to    know how many cookies were sold in the    united states that were either chocolate    chip or oatmeal raisin and i can do that    as well with some if and s    once again i’ll open up the function    arguments    and right here where i have my criteria    for the cookie type    here i chose chocolate chip cookie i’ll    remove that and here let me highlight    chocolate chip and oatmeal raisin so i    could select two different values as my    criteria and then i’ll click on ok and    here i get two values back one is 28    which is the sum of chocolate chip    cookies and then i get 32 which is the    sum of oatmeal raisin cookies sold in    the united states    now i get them back separately so each    criteria comes back as a separate entry    now if i just want to get this back as    one    right up here    within the formula i can click here type    in sum    insert a parentheses around the sumif    function    then hit enter and here it’ll add up    those two values and so the sum of both    chocolate chip and oatmeal raisin in the    united states here if i highlight these    cells we see at 60 and here i get 60    back    down below we also see average if and s    counts if and s and then also min and    max so once again for all of these    different functions you can have    multiple criteria now you might be    wondering well with sum if and s you    could just have one criteria at which    point it’s exactly the same as sum if so    you could just always use sum if and s    and then ignore sum and if because this    not only does one criteria but it also    allows you to add in multiple criteria    moving on to the next worksheet we’re    going to look at some functions that    allow you to manipulate text and these    can be a big time saver    here’s the first one we have the kevin    cookie company name but i forgot to    capitalize the first letter of each word    now of course i could go through and i    could change that or i could use the    proper function    here i’ll type in equals proper    open parentheses and here i can place    this text in close the parentheses hit    enter and here it applies proper casing    to every single word    the next function is the trim function    and here once again we see the kevin    cookie company and here i have some    leading spaces in front and i don’t want    those now of course i could simply    delete those but once again if you have    a lot of rows of data that might take a    lot of time here i can type in equals    trim    select this text close the parentheses    and here it’s automatically removed    those leading spaces    next we have a set of functions that    allow you to concatenate or join or    combine text together so here i have    kevin cookie company and each one of    these references is in a different cell    but i want to bring them all together i    could eat i could enter an equals concat    and you’ll see that there are two    versions there’s concat and then also    concatenate    can cat is replacing concatenate so i’d    recommend using this one this is simply    here for backwards compatibility concat    has a few advantages over concatenate    and that’s the preferred one to use    here i could simply copy all this text    close my parentheses and hit enter and    here you see that it brings together all    of that text now of course i don’t have    spaces between these different words    so up here once again let me go back to    the function i’ll delete the text or the    range that i entered there and here i    can click on the first one place in a    comma then i could insert a quote a    space a quote another comma and then i    could click on the next text and i could    also go through and do the rest of it    but i’ll just leave it at this    when i hit enter now you’ll see that it    includes the space so here i can cat    this word with a space    with the next word    now instead of typing in concat you can    also use an ampersand so here for    example i will enter equals kevin insert    an ampersand and then cookie and then    ampersand and company and there you see    it combines all of them now of course    once again i have that same issue with    the space missing so here i could insert    a quote a space a quote another    ampersand and then hit enter and here    you see it’s added to space and i could    do the same between cookie and company    but by far the easiest way to join    together text is to use the text join    function here i have kevin cookie    company i’ll enter equals text join open    parentheses and here i get to specify    what the delimiter is or what’s going to    separate each one of these cells full of    text i’ll enter a quote a space a quote    a comma and then i could say what should    i do if i encounter an empty cell here    i’m just going to ignore them    put in a comma and now i can select all    the text that i want to join i’ll    highlight these three and then hit enter    and here i get kevin cookie company back    with a space in between each word so out    of these three options of concatenating    or bringing together text text join is    by far the most powerful one    moving on we have some additional    functions here right left and mid and    these allow us to take out text from the    right hand side    the left hand side or the middle so what    do i mean by that well let’s say that i    want to pick out the zip code from this    address i can use the write function    i’ll enter in right    and then i also like this address as my    text a comma and then i need to define    how many characters do i want to take    out from the right and here we can see    the zip code is five characters i’ll    type in five and then hit enter and this    pulls out the zip code next we have    another function called len and this is    an abbreviation for length so i could    use len to figure out how many    characters make up the kevin cookie    company i could type in equals len open    parentheses and then i can put this text    in close parentheses and there we see    that it’s 20 characters long    next there’s another function called    find and then there’s one called search    and they essentially do the same thing    but there’s one key difference    find is case sensitive and then search    is not case sensitive    here in this example let’s say i want to    find out what is the position of the at    character    within this email address and i could    use either find or search to find this    since at isn’t case sensitive    here i can insert the equal sign and    let’s use find i’ll type in the function    name    and i want to find the at symbol here    i’ll insert it in quotes since i’m    searching for text    put in a comma and i want to find it in    this email address    i also have an optional parameter at the    end but i’ll leave that just blank    here i’ll press enter and i can see that    the at symbol is in the sixth position    of this text    now you might be wondering what good is    it knowing what position the at symbol    is in well you can really get some nice    power when you combine this with other    functions so let’s say that i just want    to pick out the name portion of this    email address and i don’t care about the    at or the domain at the end here i can    start combining some of these text    functions for example i could use left    together with find    let’s enter in the left function now    remember with left it’ll keep the    leftmost characters and i have to define    how many characters on the left i want    to keep    here i’ll select this text    then i’ll enter a comma and now i have    to define how many characters i want to    keep now i just want to keep five    basically right up to the at symbol and    i could use the find function to tell me    where that at symbol is so the same    function that we entered right up here    that gave us six back    here i’ll type in find and once again i    want to find the at symbol    here i’ll enter the at symbol    and i want to find that within this text    now i’ll close the parentheses and close    the parentheses again    and when i run this now i see that it    has kevin and at    and that’s because here it kept the left    most six characters    so one thing i could do if i go back    into the formula or the function i can    subtract one from the results of the    find function and then i can hit enter    and it just keeps kevin because i don’t    want to include the at symbol    the nice thing now is this is completely    dynamic so if i change the name to say    sue here it automatically updates to sue    so this shows you the power of when you    start combining these different text    functions together    moving on to the logical function sheet    here we’re going to see how you can add    some logic to your spreadsheet and first    off we could simply check is something    true or false    so here i’ll enter in the equal sign    again and is the kevin cookie company    equal to    miss fields    now i hope all of you know the answer to    this but of course not the kevin cookie    company is way better than miss fields    now this simply compares those two text    values to see if they’re equal and of    course they’re not so it says false but    you can also use this for numbers so is    10 equal to 5 and no that’s false    here i could also say is 10 greater than    5    and that’s true    here i could also say is 10 greater than    or equal to 5 and that’s also true and    here is 10 let’s say less than 5    and that’s false so just a quick look at    how you could compare different values    within your spreadsheet    here too you could also check if    multiple criteria is true or false i’ll    enter in an equal sign and then type in    and    open parentheses and here i could say    let’s say is 10 greater than    5    then i can insert a comma and let’s say    is 10 greater than 11. and then i can    close my parentheses and that’s false 10    is not greater than 11 but it is greater    than 5 but both conditions have to be    true for this to come back as true so    let’s say i entered a 9 in here that is    now true    now instead of using and i can also use    or i’ll modify the function to or    and here it says is 10    greater than 5 or is 10 greater than 9    so here if i put the 11 in it’s still    true because 10 is still greater than 5    even though it’s not greater than 11.    moving down now we’re going to write an    if statement this is kind of feeling    like programming a little bit we get    some if statements on our spreadsheet    and here’s the business question that i    want to answer do we sell more chocolate    chip or oatmeal raisin cookies    here we just look at these numbers and    we sell 97 chocolate chip and we sell 77    oatmeal raisins so of course we sell    more chocolate chip but can we use an if    function to answer that question    to make this easy let’s click on the    function helper up here and let’s type    in if and here i see the if function    let’s select this one    now here i can run what’s called a    logical test    so let’s say is 97    greater than    77 that’s my test and here it comes back    true    and then i can define well what happens    if it’s true so here i’ll say we sold    more chocolate chip    but if that’s false and this is not    greater then i could say let’s say    oatmeal raisin    and then i could hit enter so do we sell    more chocolate chip or oatmeal raisin    well we sell more chocolate chip but    let’s say that oatmeal raisin let’s say    we had a big day of sales and i sold 150    well now it automatically updates and    we’ve actually sold more oatmeal raisin    now    moving down we can also use and together    with our if statement    so here for example does chocolate chip    sell more than all other cookies    so let’s once again insert an if    statement here i’ll click on the    function helper and i’ll select if again    so here i can enter my logical test    i’ll type in and remember this is just    like we did up above    and i want to run two separate tests    first off is 97    greater than 150.    and for my second test i want to check    is 97 greater than 50    then i’ll close my parentheses and if    true    let’s say yes    and if false i’ll enter no    then i can click on ok    and here we see that does chocolate chip    sell more than all other cookies no we    don’t oatmeal raisin sells more but    let’s say i typed in 96 here    now it’s yes    now just like we looked at previously    here i can take this function    and let’s use it for this or statement    so does chocolate chip sell more than    oatmeal raisin or sugar cookie i’ll    paste in this function and instead of    using and i’ll type in or    then hit enter so does chocolate chips    sell more than oatmeal raisin or sugar    now let’s say oatmeal raisin sold let’s    say 150    well it still sold more than sugar    cookies so it’s still yes but let’s say    sugar cookie also sold 150 now it shows    no    so that’s how you can use and    and or within an if statement    now you might be wondering can you    include an if statement within an if    statement and you absolutely can    and here in this example let’s say i    want to know the price of a chocolate    chip cookie    so here we see all the cookie types and    the associated price and i can use a    nested if to figure out what the price    is for this cookie type let’s work    through it    here    i will enter the if function and first i    want to check    is this cookie type    this one right here    and if it is well i want to get this    price back    but if it’s not well we want to then    check the next row so here i’ll type in    if again    and here i want to check is this name    equal to this cookie type    and if it is we get this price back    and if it’s not then we want to run one    more if statement    so is this cookie type equal to this    cookie type and if it is this is our    price and if it’s not then we’ll say    unknown    and then i can close the quotes close    the parentheses close the parentheses    again and close the parentheses again    one thing that’s really helpful when    you’re closing the parentheses you’ll    see color on the parentheses so the    first one’s black the next one’s red the    next one’s purple and here you can tell    that i’ve inserted all the right number    of parentheses    now i could hit enter and i could see    that the price of chocolate chip is 10.    but here i could type in sugar cookie    and there i see the price is 5. so    that’s how you can use nested ifs to do    that    here i’ll enter in formula text just so    we can see what all of the formula looks    like    so you’ll notice it’s a fairly long    formula and it starts to get pretty    complex now there’s something new called    if s that makes this a lot easier    here i’ll type in if s    and here first i could do the first    logical test so is this cookie type    equal to this one    and then if true i want this price    the next logical test is this cookie    equal to this one and if true i want    this price    and lastly is this cookie type equal to    this one and if so i want this price    now i can close my parentheses and hit    enter and here i get five back i’m going    to move it over one and once again let’s    show the text    for this function and you see it’s a    little bit shorter and easier to read    than what you see up above and in fact    with if s there’s really no longer a    need to use nested if functions anymore    instead you could simply use if s and i    think you’ll find that it’s a lot easier    moving on to the next worksheet we’re    going to look at look up functions and    this allows you to look things up    so here for example i have a table with    cookie types the associated price and    the associated cost and let’s say i have    the question well what is the price of    an oatmeal raisin cookie    so to look that up i would say well    oatmeal raisin well here’s oatmeal    raisin and oh here’s the price so i just    looked that up but we can have excel do    that for us using the function vlookup    here once again let’s click into the    function helper and i’ll type in vlookup    and here it is let’s click into that one    here first it asks me what is the lookup    value so what do i want to look up well    i want to find the price for oatmeal    raisin i will select that    then it asks me well what is the table    where you want to find this well this is    my table so let me select the table    and then it says well what is the column    index number what is a column index    number well that’s what column do i want    to get back when it finds a match so    here it looks in this table it’ll look    at the first column and when it finds a    match here i want to get the price back    and the price is the second column of    this table so i’ll enter a 2 here    now for range lookup i could have an    exact match or a partial match now i    want just an exact match so i’ll type in    false but once again this is optional so    you don’t have to fill this in it’s true    if it’s omitted i’ll click on ok and    here see the price of oatmeal raisin is    eight now here i could type in sugar    cookie    and here you’ll see that the price is    five so here it automatically does the    lookup for me    now right down below there’s another    function called x lookup and this    replaces vlookup it’s a newer function    that’s available in the newer versions    of excel and it has some key advantages    over vlookup but first let’s do the same    example and see if we get the same    result once again let’s open up the    function helper and here i’ll type in x    lookup    let’s see if it shows up and there it is    here it asks me what is the lookup value    and that’s what i want to search for and    i want to search for sugar cookie    and then it says well what is the look    up array and so this is a little    different from vlookup i want to look in    column a for sugar cookie here i’ll    select column a but i could also select    these values but i want to select the    column    and then it wants to know well what do    you want to return and i want to return    the price    here i have some optional parameters    like what if it’s not found do i want to    give an error message back i have match    mode here if i scroll down you’ll see a    few other options so we have a few more    options compared to vlookup i’ll click    on ok    and here we see i get the exact same    thing back and it works the same way    here i could type in chocolate chip    and you notice that i get the price of    10 back so why would i ever use x lookup    compared to vlookup well let’s look at    some of the advantages what if i wanted    to get the price back and i wanted to    get the cost back    once again let’s click into the function    helper and for the return array right    now i have it set to just give me the    price back    but here i can highlight these two    columns    and here i get two values back i get    both the price and the cost so i can get    an array of values back    also let’s say that the cookie type    column was the third column and price    and cost came first    vlookup wouldn’t work anymore when you    use vlookup the column or the value that    you’re looking for has to be the    leftmost column and then the values that    you return have to be to the right of it    but with x lookup you can return any    column even if it’s over to the left    next i want to show you some date and    time functions and these are really    simple they’re actually functions that    you don’t even pass any arguments or    parameters into so let’s say you want to    get today’s date back you simply type in    equals today open and close parentheses    and the reason why is you’re not passing    any values in when you hit enter you get    today’s date back and down below you can    also type in now it works the same way    as today except instead of just getting    today’s date back you also get the    current time back    here we are on the very last worksheet    you’ve made it just about to the end and    so i wanted to give some bonus tips of    how you can work with formulas and    functions    on this sheet i have a number of    different numbers and some of them have    formulas and some of them are just the    number on its own    here you’ll see it’s a mix of both    so how do you quickly identify all the    formulas and all the numbers well you    can press the shortcut key control    together with the tilde character    and that’ll automatically show all of    the different formulas and functions on    your sheet    here you’ll see that i have a few    different formulas and here i reference    a few different cells and i can press    ctrl tilde again and i can toggle back    and forth between those views so that’s    a nice way to just view all of your    formulas    now let’s say you just want to see all    of your formulas and maybe you want to    highlight them    let’s click on the home tab up on top    and over here let’s click on editing and    then here we could click on find and    select    here you’ll see the option for find    formulas i could click that and that    will highlight all the formulas on my    sheet and now here i can apply some    color if i just want all of my formulas    to stand out and once again if i press    ctrl tilde once again you can confirm    that these are all of the formulas on    the sheet    now one more thing i want to show you    when i click into this cell you’ll    notice that it references two other    cells and when i click into this cell    this also references other cells    so let’s say that you’re troubleshooting    a function and you want to find all the    precedence or you want to find all of    the dependents you could very easily do    that    here with this cell selected i could    click on formulas up above and here i    could trace all the precedence so this    cell relies on these two other cells but    here if i go back to formula here i can    trace the precedence again and it looks    like this cell relies on these two other    cells to get this value so this is going    to help you as you’re auditing all of    your different formulas    here if i click down into this cell once    again i can click on formulas and here i    can trace all of the dependents and this    is the dependence    if i want to undo all of this here i can    click on remove arrows but a nice little    bonus trick to help you audit all of    your formulas alright well hopefully now    you are a pro at formulas and functions    in excel    to watch more videos like this one    please consider subscribing and    i’ll see you in the next video    [Music]    you    

admin

Leave a Reply

Your email address will not be published. Required fields are marked *