Lessons

Lesson Info

2:30 pm - Basic Formulas

We are going to be doing this segment on formulas were going to jump right into basic formulas were going to work with something called auto some and we're going to go into all kinds of different things so the first thing I think it's important to do is just going to take a look at sort of finished product of what what a formula might look like head of times we've actually seen some of these things already when we worked with the invoice and it was the first thing we looked at so one is kind of do a little refresher what we're about to get into in terms of what a formula looks like and this is going to be a formula that's going to be with also known as a function which we talked about we're also gonna step back we're going to do this some basic basic format to see isumi formula just you haven't understanding off what they are all right just you can understand a little bit about how to put together a very simple formula in terms of mathematics of add subtract dividing all those kind of ...

things all right? So the first thing I want to do let's go to the day one session for now basic formula's exploration invoice this is something we've already seen all right, so we're gonna go ahead and work on that and remember where we were where we saw under our amount, when we were typing all this stuff in here where we had inside of the amount column, we had this which is known as a formula, so we had equal sign e eight and we have this little asterix in there and that's what's known as one of our operators to be able to do a formula which is going to be multiply. So the basic were saying multiply this times that which is going to be e eight multiplied by f eight and come up with a formula just like that. So if I want to make it happen automatically, I just have the formula built and already this is kind of the same thing when we do sort of a a pre format it's the same idea what we're going to do a pre formula ahead of time so it makes it nice and nice and easy so if we're looking at this one right here and we know how many hours were going to do, we just go ahead and to say twenty hours and then I hit tab to go to the rights and then it's waiting you can see here is waiting me to put in something else to put in this formula so what's my rate, my rate is going to be sixty five I hit tab and like magic this just comes right out just like that all right it's going to the same thing for all of these things because I preform out of this tohave come this formula in here right? So pretty straightforward and all I did essentially was typing equal sign a nine and then the asterix f nine and that just makes the formula happen just like that and then if I were to change this let's just say my rate goes up all right for this one I made a mistake or I'm reusing something I've a different rate for a different project you're going to see if I type in seventy five now it automatically updates also because I'm now because that formula that's in here is linking to this cell you can see there's a lot of stuff you'll see all about that because basically it's not saying seventy five times twenty five it's saying e eight times f ate so whatever's inside those cells it's going to make it do that you're going to see here just a very simple, straightforward example of that now and we come down a little bit further you see I have another formula which is known as a function so this is doing a sum of h eight and then there's a colon in there and it says h twenty two so what does that mean h ate through h twenty two basically everything that's going to go from here, which is a chain all the way changed twenty to add the month. So let's let's, take another look at this. Right? I'm going to do ten hours with this one. I'll do sixty five an hour here and look, keep your eye on this down here because I added this up. Now this is now adding up so it's all linked up this magical flow of all the formulas because I have everything right in there. I'm going to show you how to do all this stuff, working with auto some in a little bit. Or you could also manually type it in if you like. You can see here how nice that is then I have also tax which is actually just manually put in here. So I just typed in eight point five percent and then what I've done here to establish what is the tax going to be? I've said let's, do a tsh twenty three multiplied by h twenty four which is this one right there. See? H twenty four h twenty five right there, that's actually twenty twenty three times twenty four. You can see it's a showing what this is going to be. So I know how much tax to put in there this basically comes right up as that so. Now I know how much that is based on the taxi these thesame this times this comes out to be that so I know how much tax is going to be when I finally yield the product, which is going to be age twenty three plus age twenty five plus age twenty six okay, so hopefully that makes sense. Um and hopefully you are you're seeing the screen as I'm doing this, okay, good. So now this is just an example of how that works is just an example of one particular type of formula how this could work here and you could just see how it's, pretty straight forward is linking up one thing to the other to the to the other and everything is all you make one change here we'll do another one a student to ten okay, we'll do fifty five this time and now everything changes all right? So that's what's the beauty of working with these forms so we're going to start doing ah few these things, but an invoice might be something that you'll be working with quite a bit if you're an independent contractor. So this is nice to be able to just kind of be able to create temple it's just off of this where you can have a formal where you just plug in the numbers and then everything just happens automatically okay, so let's move on let's actually work on something where we can when we can actually take some stuff and work with with auto some and also averages and some of these others functions that we can work with. So let me take us now to where we have basic formulas, so this is day one session for basic formulas, and we're going to just want to warn you, we're going to do things kind of a little bit a long way at first, and the reason why I'm doing that is mostly so you can sort of get the exercise and the theory and fundamentals behind it all. So you have kind of an understanding of the of the just sort of basic foundation of how formulas work so let's, just say, I want to do a total of quarter one just going down just like this. So every formula as we know I've tried to reinforce this starts with an equal signs, no matter what, it starts with an equal sign. So even if I'm going to do a very basic formula like you say I say to you, plus two, right, which I could do right? Because excel is sort of a calculator as well, I hit return and see, I get just two plus to excel is waiting for that equal sign it won't know that you want to do that unless you have an equal sign and now when I do equal sign two plus two, I get what I'm looking for it to equal sign fifty multiplied by one hundred, then you see, I get the formula c because I have that equal son in there so that's really, really critical, so let's do just some basic formulas were going to get to auto some eventually, but let's, just do it basic foremost were going to say, equal sign, and I'm literally going to type in this time I'm gonna say c five because that is my cell address I'm just going to type it in, plus c six plus c seven plus c eight and no plus at the end, I noticed how cute that as if it's a little color box around all of them, which is kind of cool, we know that the color box corresponds to that I sell value that's in there right there, so you can see that the cool green and the purple and all that kind of stuff, right? So that's nice ofyou corresponds to it and make sure you don't have a plus sign of the end because it could be habit that you get into because you just kind of clicking away typing away so we're going to do now is simply hit return and then that's there you can see very easily we've added these things up now let's change apple from the sixty three fifty four let's change that to say eight thousand and you'll notice when you had enter your total is now going to change because the total is linked up to the cell values all right that's why it's important when you add these things up you don't say eight thousand seventy five hundred seventy six hundred you want to actually add up the cell values okay all right let's do it another way this time this time let's do equal sign and what I'm gonna do instead of typing it in I'm just going to simply click when I click on that on that sell value it actually brings it up for me and actually says d five then I'm going to do plus d six plus seven plus t eight just like that all right so a lot faster than having the type it in you and you don't make any mistakes cause you're going right towards the source so that's really nice just bam bam bam you go right to it just another way to be able to do that um did I miss one more let's go d plus d nine can might have only done okay there we go all right good you could see how that works really nice really straightforward ok now let's say I don't want to do that ever again let's just say I got way too much time and way too busy way too important I got other stuff I got to do and I want to be able to just do this as quickly as possible so we've talked about auto fill already we talked about auto fill and other things when we were doing auto filling the word california we auto filled for formatting you recall that we can also do auto fill for formulas rights we can actually auto fill this formula so it applies to these guys as well so if you recall my my my auto phil is gonna be in the lower right and all I do now is simply click and drag to go across just like that and it knows to automatically change this from the column number as d to the column number is e you have see here I have d here and I have either and I have f here so this is what's known as a relative reference a relative reference so basically it knows to do it based on the relative location of that column with respect to the first comment you auto filled from right again that's what's known as a relative reference and I think well if we have time I wanted to get into what's known as absolute references which is something that could be important for you as you're moving forward okay, so let's go ahead and just do unexamined of again right here let's do we're going to do a function this time under total right? So instead of doing the sell plus cell plus cell going to a function that's called the sum function very straightforward remember what a function is a function is going to allow us to do a text version of what we just did right now what we just did right now was more of a you know, a number of versions but we're going to do a text once going to say equal signs some and subsequent to this we're going to equal sign average okay? So we're going to just go from there so all we do now is simply type an equal sign and then some and then open parentheses and what we're going to do now is simply highlight see five through five I noticed what it does there it actually puts in the first cell that I selected the last so I selected and a little colin in there so if you recall from one of the last segment that calling just means through just saying see five through f five sum them up so that some is just basically ah ah function that's telling okay excel just go ahead and add up all that stuff within that range okay it's essentially just a range was going to go and hit return and that's done and that happens and that's great and then guess what I can auto fill down below here so anybody remember how I can auto fill for the rest of them are having toe do anything else just double click it just double click right here very good double click and steve damn it just pops right down and doesn't automatically and there could have been a hundred of them right below it will be that fast so this is again this is auto fill but we're doing auto fill for formulas all right? We're going to do that same thing when we get to the average minimum and maximum now any questions on that already doing? Okay alright now you'll notice I get these funky little flags here kind of telling me that like what something's going on here so that's an error of some kind of a potential error, so I click on this right now it's going to tell me formula omits adjacent cell so what? I want you to at least understand that like, if you do have some kind of weird error that's how it's going to show you're gonna have, like just kind of things that just don't make sense to excel or it's going to it's tryingto actually potentially guess that there's something wrong so it's giving you a suggestion to say like I think something's weird going on here or you missed something so what we talking about here? Formula omits adjacent cells essentially means that you know what jason just means next to right. So what are we talking about here? It's saying that? Hey, you know what? You added all these up, but there's, another number over here, did you? Did you mean to forget that? So that's really nice of excel. Thank you very much. So we're going to say, you know what? Thanks, but no thanks. So I could easily just click on this right now looking to drop down and I'm going to say ignore error very easily. In addition to that, I can select all of them click on the drop down and say ignore air another all gone. So I want you to just at least understand how excel again is trying to communicate with you. So we see those little flags in there, it seemed like, hey, you know what, there's a potentially an error there. So I just kind of be aware of those things as xl brings them up. Okay, so let's, do a couple a few more or other formulas. I want to actually do a little bit mohr of working with with some feature so let's, just imagine we want we have a situation where we want to sum up just some things that were kind of just sort of not next to each other and we can do it in a few different ways we can do it where we use the function, the somme or weaken do where we're actually just using like the plus minus operators, you know, all those different things they're so let's do it the first way very straightforward equal sign and then I want to add up let's just say apple quarter one plus apple quarter three plus toshiba quarter three and then I say okay and that's it right and that's nice, so I could just very easily do that that's cool all right? And now something we're very similar we did initially all right, everybody got that pretty straightforward because that's, what should be a little bit of review now if we want to use a function to be able to do this, we're going to do this. We're going to say equal sign some and then open parentheses and we're going to put in all the ones that we want to sum up, but we're going to separate them by commons so it knows to some of everything in there that's going to click on the one I want and I was going to do apple quarter one apple quarter to make sure you do apple quarter one comma apple quarter to comma and I'm going to say apple I assume the hp quarter three comma and just know that I could keep going with it just just choose a whole bunch of them make sure you separate them all by commas and I'll do that many it enter and I'll leave it up here so you can see it what I've done okay, so this is another way to do it so instead of putting them putting plus signs in between them all you just having this this function the beginning saying some remember somebody asked a good question from the home audience just about what's the difference between a function and a formula you see here there's no plus sign minus sign or anything it's just saying case some up everything in here and they're all separated by common because they're not next to each other if they are next to each other it's the colon all right if you remember that it's the colon is going to be c five through h five so is going to see five colin h five okay, so that's just um some basic formula stuff here now let's add up with me let's do an average for quarter one or we're going to do just quarter one h p all the way to quarter one del so we're going to do that using a function so just go ahead and type in equal signs you probably know by now and type out the word average open parentheses and very simply, I can now click and drag from the sea five all the way down to see nine and then bam! It just tells me, see five three c nine will get averaged okay? And now what I'm gonna do is I'm going to say, all right, you know what let's average out all these guys across s so you'd probably guess what I'm gonna do. What am I going to use? Doubleclick will not work going across that's a good guess, though it will not work. Going across the default is to go down yeah, just click and dragon across and make sure you have your the auto fill handle here and come across there and then they're doesn't magically again. This is going to be a this is going to be a relative reference saying, g, this is f this is easy if you look at the formula, it tells you exactly what it does here, okay? Now, earlier on, when we were working with the texts where we're working with upper, we're working with proper I took us into the dialogue box for formulas on the mac side. This time we're going to do it on the pc side or we're going to work with a showing you how you can work with the formulas in here so it's a little bit different but you'll see that accomplishes the same goals so I'm on the pc side and notice I have the same thing fx which is essentially a function is what that that's the abbreviation for it so I want to do the minimum for this particular row or whatever terashima column and I wanted to be here and see thirteen so make sure it's selected and I'm just going to just click once in that fx and again this is just on the pc side so if you already comfortable with the way how it's done on the max side from the mac just go out and watch along here but you see it does a little bit differently where the categories you can get to them right away just by working with all these things here so just so you know here if I go to to text because I want you to see how that works I got a text it actually filters them out for me or not this is not text I want you to like see the difference here so when we worked with um upper see there it is right there all right now if I go back to these and I'm going to do let's just say all for right now and I know what I want to do is I'm going to do minimum so now if I simply just click on one of these and type out the letter m right, so I like it. I checked on m and then it comes right up. It takes me to that part of the list and there it is minimum okay, so once I choose ok it's going to take me to this cool little wizard here and saying ok, well, you know what? What we're going to be doing here from minimum of what? And I just kind of give you a little bit of ah don't typically do it this way, but I want you to see how how this type of interactivity works working with excel so you can actually see in case you want to do it so this is going to allow me to then choose what I wanted to be so I'm going to do now is simply click and drag to go from here from here to here and there it is all right? So what's gonna be my minimum everything and I've already put in the formula there for me. So ah lot of people are just a little bit afraid of formulas because of the typing part of things and because it's really easy, tio either get confused or make typos. This is the way around that this is a pretty simple one minimum and maximum causes just well, we'll take a look at what the sin taxes in just a second I'm going to look okay because I'm done and you'll see here the minimum in this case is seventy five forty six and he got it right what do you know okay and then for d column d let's do that the manual way meaning we'll just type it in man open parentheses and just highlight it just sound like that and I need to get it right yet forty five ninety eight all right and then of course I could go in auto fill going across and I need to do there's no mo all right there we go and then you could probably guess with the sin taxes for maximum if minimum is men when you think maximum is uh you got it so you just go and do that same thing however you want to do it just typing and max highlight everything hit enter and then just go straight across and there you go auto philip now this next segment is all about is all about counting and that's actually gonna be very very helpful when you have a whole lot of stuff you want to know you know how many clients that I work with or how many projects that I work on or how many sales that I have or something like that you could do that so notice how there's actually two different things here because excel has the ability to either just count numbers or count everything including text and numbers all right so there's going to be two different types of syntax for that so if I want to be able to count you know all of these to be able to see how much then I could go ahead and just two normal count so countess simply count that's what your sin taxes for this I'm just gonna go ahead and say equal sign count and remember the jeweler cheat sheet right now you want value to all that kind of stuff here and I want to actually show you something because I'm going to do it in kind of a funky way so you can understand what count does and what it doesn't do so I'm going to go and highlight c four and go all the way to f nine and I'll show you why I'm doing that I have that there and I'm going to go and hit enter hit returned and notice the number that comes up is twenty when I highlighted twenty four tonight see this is one that goes all the way down to here and then I had six that I highlighted times four is twenty four but it only came up his twenty because what count does it only counts in number understand this if you want to count numbers you use count if you want to use you want account anything else in addition to numbers you do what's called counts a and there's the pops up there counts the number of cells and arrange it or not empty okay, so I'm going to do that and this time if I highlight it's time to go from clients all the way down to here right now it actually shows me the accurate number everything are highlighted it's like whatever is not empty because if there were empty cells there it actually wouldn't count it so it's smart enough to know that okay, yes, those were both that's both uh text as well as numbers but it's also not empty, so if it was empty it would have counted it all actually kept coming down it's actually add on if you watch me here somebody asked a good question earlier about um how do I change the formula? So I'm going to change this from the nine to be ten I could do that really easily either by changing this to be ten here or I could very easily click and drag this town do you see that changes for me? So I do that and guess what? My number does not change at all even though I've selected mohr because guess what? That's an empty cell and excel understands that so it's not going to count that so nice really really fast way are being ableto just kind of count things um and that I could really come in handy when you have a lot of stuff okay, and then if you want explore on your own, you might be thinking like what if only one account certain things we won't be getting into that but there's a there's a function called account if function so some of you might say, well, count if on ly if it has a certain word in it or something like that, right? So you're working with this particular project only one account those projects because you have all your projects sort of intermingled so it might be planning a seat for you to be able to explore on your own account if function so you could go ahead and try doing okay now we're going to move on and a little bit too something auto some, but I want to show you one of my favorite favorite features that people who know excel for a long time just kind of ignore this they don't realize it's there is again one of the things you take for granted I'm sure you had to do that on the p c it's a little bit different on the mac side in terms of the stuff that it shows you but it's still, you know it does it does what it needs to do with certain limitations, so let's just say, for example, you're working on the spreadsheet and you want to just know very quickly how much formula how much you know, numbers kinda interact with each other. You want to know how much you know some is or the maximum, the minimum all it's something you don't want to create a formula for it. You don't have to even take up screen real estate to be able to do that. So excelled gives you this fantastic feature that basically all you do is click and drag to show it. So if you take a look down way on the bottom, right on the edge of the program, you're going to see as a highlight, these numbers watching you do this and you click and drag notice what appears down in the bottom on the mac it's going to be probably just one number on the p c we should have at least maybe three numbers because I'm doing that. I have some count and average just like that without actually even doing a formula. Now if I want to add mohr onto here so there are showing more stuff that's what I'm gonna do, I'm gonna right click and I have all these things hidden here. This kind of thing that people just don't even know exists so hidden in here, so I can now say let's, bring up numerical account. Let's bring them out minimum let's bring up maximum and you see all these things were here now on the max side of things you're gonna get let me actually just jump over to this see what I'm doing this it only shows me the sum but I do have the ability to toggle between all of these you can only look at one of the time okay which isn't so bad but that's just that's just how it is because you just kind of see that there I was on the pc side you're actually going to get all of them all together on one screen still very very cool I mean it's just a huge time saver and you're like huh? How much is that? Okay, great and then you don't have to do any formulas you don't have to move it anywhere anything like that now. Likewise, what I've been doing is just doing continuous sells member continuous was basically means next to each other and what am I going to do then to do things that are not next to each other? Okay, I'm going to do the command will command key on the mac hold on again this is a little bit let's see command here we go ok see you on the on the pc you're going to do control on the mac you're going to do excuse me on the pc you're going to control the max your new command and notice how I'm getting these disparate um no one continuous cells and see it's still adding them up because whatever selected it automatically does the math for you which I just think is so cool that that even exists that you know they didn't have to do that and it's just there you know? So I think that's quite kind of them all right? So any questions on that formula's all right on the on these basic formulas so what we're going to do now is we're gonna go to the auto some going to see the auto some is going to have a lot of different features not just summing but also for working with different kind of formula is very close to what we've been doing already but without having to type anything in hence the term auto it just needs to be clarified if you add in more column so you have to add in another computer company or more rose does that screw up your that's a great question typically and like we can actually do that right now the next segment what we're gonna do is actually adding in new rose after we do formulas but that's a great question most likely knows if you look at this formula right here actually this is well when we go to auto summit will do it it'll automatically put it in there for you. So if we add this one in here this's auto some I'm goingto listen a little preview of doing inserting a new row, so I'm just going to right click on road number eight I'm going to say insert and then if I put in some numbers here, okay, this one likes to see your c one through okay to that change its c eleven we want this thing see oh no, no, this is the one going across. Sorry, thiss more this doesn't actually make sense for that because this formula was this going across. So let me do that one more time and I'm going to do my formula some and it's going to be all these guys here and then there's that, and this is summing up all these going down what I had before wasn't something going across now, it's going down as if I add in another column seem you knew another road and I'm just going to say twenty thousand see how it adds up, right? There are two thousand twenty thousand, okay, so that added it up right here automatically, because if you look at the formula, it said g five g ten before and it's within that range, it automatically adds it into it.