 statistics and Excel typing mathematical equations in Microsoft Excel get ready taking a deep breath holding it in for 10 seconds and looking forward to a smooth soothing Excel first a word from our sponsor yeah actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but but that's okay whatever because our merchandise is is better than their stupid stuff anyways like our trust me I'm an accountant product line yeah it's paramount that you let people know that you're an accountant because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions if you would like a commercial free experience consider subscribing to our website at accounting instruction.com or accounting instruction.thinkific.com here we are in excel if you don't have access to this workbook that's okay because we'll basically build this from a blank worksheet but if you do have access just two tabs down below this time example and blank example in essence answer key blank tab blank worksheet where we will work through the practice problem together let's take a look at the example tab to get an idea of where we will be going now note when we think about actual mathematical formulas and equations things that look like this on the left hand side it can be a little bit confusing when we then try to work through practice problems in excel because we usually think about in excel functions which start with say an equal sign like the sum function and then we're going to put some data inputs in order for it to give us the result of the functions however oftentimes it's useful for us to actually type down the equations and so so we're going to practice doing that here will actually type the equation in place and it also can be a little bit confusing when using excel to use some of the symbols which often are like Greek symbols for example and so the common symbols will be using in statistics might be like an x bar a sigma a mu sigma squared for example and there's a couple different ways that we can type those now note when you're working in excel then if you can convert something to a function like if you're going to take the mean of something you might use an average function it's quite useful but sometimes it's useful to think about things algebraically too so the good old paper and pencils sometimes is a good way to go but the other thing you can also do is take a complex equation and basically break it down in kind of a worksheet in a table type of format which is often something that's useful to do in excel so we'll get kind of an idea of what I'm talking about later but for now what we want to do is just say well how can I type something like this into excel and how can I basically get some of the letters that I would like to use in our mathematical equations in excel so I can represent them in ourselves let's go to the blank tab to check that out so if I just want to add if I want to type an equation I could go to the insert tab up top and you've got the symbols on the right in the symbols group so if I just want to add a symbol I can go here if there's a particular mass symbol that's like a Greek symbol or Greek letter or something and or I can go to the equation so here let's add the equation now if I hit the drop down you've got some standard equations so the ones we're working with we might be able to find you know in the standard equations but let's practice typing it in here that gives us our formula bar now if we go into our formula bar then we could then add all of the items within a formula so we can do that by going to the equation up top so remember I've added a formula here I'm going to scroll in a little bit and if I'm off of it I don't see the tabs up top but if I go on to it then I've got the shape format and the equations now I could add each kind of component of the equation and it gives you these little little helper boxes and tools which is quite useful and and you can it's tedious to do but you can build a complex formula using all of these tools however there's an easier way to do it which I think would would be useful for most people we can go to the tools over here and say I would like to write it with an ink equation and if you have a if you have and it looks like this and then it'll try to simulate what you are writing using these tools in essence which which is great however you know I'm gonna have to do that of course with just an ink pat with just my mouse so if you had an actual pen to write it would be easier but you could do with your mouse I'll do it with my mouse here so we're gonna write this first equation which is this is the mean this is one way we can represent it I'm gonna put an x here and so you can see what it's trying to do it's like a to know I put the other bar and it's like oh it's an x okay and then I put the bar over the top of it and so it reads that correctly an equal sign I'm just gonna say equals and then I'm gonna say x and I want to say this is gonna be subset one so notice it thinks it's a two then it's an x like yeah that's it and then I'm gonna put a little one next to it so it picks that up nicely and then a plus button gonna say plus and then I'll say x and I'm just doing this with a mouse so you can although it's hard to type with a mouse you can do it and if you get something wrong you can erase it down here so I can erase that too if I wanted to boom it's gone you can also circle something and it gives you other options so if I'm like that is not an x and they give you other options that well maybe I'll give it a different guess right it kind of does that so but I think the easiest thing is usually to just delete the wrong thing and retype it so there's a two so that looks good and then I'm gonna say plus and then x see that's an ugly x see if it picks it up it's still it still sees it it still gets it three I did that on purpose just to show how it can pick it up even when you do it ugly x and then plus and then I'm gonna put dot dot dot dot it picks picks that up not exactly the way I would have had it on the bottom but that's fine plus and then x to the end to the end boom oh see it didn't like so now it says plus x minus and so I'm gonna say erase that what are you thinking maybe it didn't like that x maybe it's plus is even wrong I'll make plus x in alright that looks good and then I'll put this whole thing over underline an end so this is this is for now the x was on top so I actually erased it and then I made a very large x bar on the left hand side so now it's properly picking up the x bar which is standing in for the mean or the average the mean or average often being shown in the symbol of x bar or a mu which is the Greek letter looks like a you is the Greek letter mu and it might differ depending on whether we're talking about the entire population or a sample of the population we'll get into more of that in future presentations but the average calculation then is going to be x 1 plus x 2 plus x 3 and so on and so forth up to x in in representing the number of items in the population the number of x's all of that divided by then in in representing the number of items in the population so I can go ahead and say that that looks good so let's insert it so we'll just insert down here and there is our formula now once made I can go to the home tab over here and increase the font if I so choose so we might want to make a larger formula and sometimes it might wrap it so if this gets too small I could kind of mess up the formula so you have to make sure that your font size lines up with how large the box is so it's being represented properly and I'll type mean mean calculation up top I'm going to select the entire worksheet and go to the home tab font group and make it bold let's actually format the worksheet like I normally do as well so I'm going to right-click on the worksheet format and then I'm going to make the entire worksheet is going to be numbers I'm going to go into the numbers group and then I usually go currency negative numbers bracketed and red no dollar sign I don't need any decimals so I'm going to say okay and then I'll make this my header I'll make this black and white home tab font group it's kind of like the title so maybe black but we'll do that black and white on the header now notice this obviously is on top of the cells it's not within the cell so you can have to kind of be careful and move it around when I select a column this isn't selected when I select the column generally it's kind of attached possibly to a particular cell that it's in but it's not really it's not attached to these columns for example you can also copy this if you wanted to copy it and you could paste it as an image and that could be a useful tool if you wanted to paste it somewhere else so you don't kind of mess up the formula that kind of makes it almost hard-coded in a sense so that it's so you can't go in there and type in it you can see through it here so the other thing that you could do is you could put some formatting around this so that there's a fill color so I could make a shape fill and make it white so that now it's you don't see the background of it the other way you can do that is actually make the cells behind it white I can go to the home tab font group and make the cells behind it white and so now it's it doesn't you can't see the grid lines in the back of it but it's probably better I'm now that I'm working with this I'm gonna control Z or undo to make to make the actual box white or some other color you can make it so that it stands out as its own as its own thing right maybe orange isn't too bright so something like that maybe so let's do another one I'll do another formula this is another way that we can express the mean and we'll talk about other formulas in the future and this is so so we'll practice this as well in the future but let's insert another equation and so here's my equation thing I'm gonna go into it now I have the equation up top I'm gonna make an ink equation again and just type this one in so this one so we're gonna represent the mean in a different way so I'm gonna start with x bar again this time I'm gonna type it really large I'm gonna say this is gonna start with an x on the left so it knows that that's on the left it's gonna be an x the bar so pick that up properly and then I'm gonna say equals go somewhere in the middle here equals so there we have that and then I want this some symbol so the some symbols gonna look like this to jet jet and so I pick that up then I want an n on top the n sometimes it doesn't pick up until I do the bottom bit so it didn't really see it yet but I'm gonna say that's okay I'm gonna say let's put an I dot equals one so then it saw that and then it picked up the end so that looks good on this side I want to say x and then to I sub I and so it picked up a J instead of an I let's erase that let's see if I can erase just that and then say no it needs to be an I so there it goes so now it picked up an I so I think that's right and then this whole thing needs to be over so I'm gonna underline it and then in and on the bottom boom so there we go so it formatted so now instead of putting the in like on top it put it a little bit off to the side so that's fine so we'll keep that that looks good so now what this is basically saying is we're gonna it's another way to express this right this is the sum function we want to sum all of the X's X sub I where we're going to be implementing X starting at one and keep on summing up all the X's until we get to the number of X's to be subbing in there which is n represented here and then take that whole thing that we summed up and divided by the count which is in so this is another way that we can express it that's going to be once again the mean or like the average so it's inserted so there we have it so I'll pull this down here and let's make this orange on the fill again and this how big was this one this one was like 24 so let's make this one 24 as well so we'll bring it on up to 24 so there we have it so that's nice now the next thing we might want to do is say well how can I represent some of these items in excel like an X bar for example how might I do that one way you could do the X bars you can type in an X and you can go into the insert tab up top and then in the symbols we're gonna go into the symbols here now when you're using these symbols often it's useful to go screw all the way up top to this on the left the font just being the normal text font and then I want to combine this X with something else the bar on top so I want to go to the drop down and I want to go to combining the items the binding the marks and then if you use this a lot you'll have it down here as well but up top here it is I think so it says combining overline that's what I want so I'm going to insert it and there's the little bar that it put on top so that looks good so I'm going to say okay so we have that now later we're going to be using a sigma and and we might use a mu so those are common you know statistical items so there's a couple ways we could do a sigma so I'll show you that one we can go to the insert one ways the same way insert symbols up top and we can then say again I would go to the normal text and then on the left now we're looking for Greek and Coptic Greek and Coptic and then it's you know if you don't know the alphabet here it gets a little bit confusing to find the sigma but if you use it a lot it's actually right there there's the one because this is in my recently used symbols but here it is there's that one now I'm going to insert that so that's so that's one way we can get that one and then you could do it a different way because the sigma is the Greek letter for an S so you could type in an S and and then and then I could go into you can actually go into the symbols on the left hand side or I'm sorry home tab font group and type in a symbol symbol for the font type and then it'll create a sigma that way now that only works if you just want the sigma in the cell because if you have multiple things in there it's going to type them in that font in that font style now as a symbol so sometimes that won't work all the time and but sometimes this one's hard to find you also might have a sigma squared so so if I do the same thing if I copied this sigma if I copy these down I could square each of them a square I can put a two but then of course I wanted to represent above it so one way to do that I think the easiest way is to select the two and then right click and then format the cell and then we want it to be a subscript so I'm going to say subscript and then boom and it moves it up there and it works on either format so if I type in a two and I select that subscript and right click format cells and then subscript so we can do that and then the letter mu is the other one that is often used and it's actually so one way you could do it is another Greek symbol so I can go into the home tab I'm sorry insert symbols and then again normal text I'm in the Greek and Coptic and I'm looking for a mu and so it looks like this it looks like a you so but I have it already down here so here it is it's right there so again if you use it a lot it'll you'll pull it down here into your your recently used I'm going to say insert and then you could do that as well because apparently that's an M which and then you could go and then you can go up top and say home tab and make this a symbol so you could type an M and then hold on a second that's not right symbol symbol so it's kind of tedious to use this lettering because you might say well why do I need to know this stuff but but you know that's traditionally the way these things are kind of written so it's kind of nice to use the to be able to to see the symbol and kind of have an idea of what's being what's being used there and then to be able to type that into Excel which is kind of annoying but it's possible to do okay so I'm going to highlight this and make it bordered and blue and so those just obviously once you once you have that down then you can then you can start to type these formulas in now once you have these formulas in then the question is is it easier to do something by hand with a paper and pencil and just work the algebra with a paper and pencil or is it easier and it often is a lot of times we're just not used to it to actually you know make up they basically make a table and do the algebra kind of an excel and like a worksheet type format which we're not used to doing of course because normally when we learned math we never really had Excel to help us to be putting something in so sometimes of course something like the average function in Excel would be quite useful to use but it's like that takes a one-step process you might want to actually see some subtotals along the way like like calculating the numerator and the denominator and if you want to actually see what's going on with the formula then of course you can do it algebraically but you can also do it in a table wise where you basically are going to be calculating the numerator and you can actually break it out in a table or a worksheet kind of style and that could be quite useful to understand the things and also for the data input if you want the actually the components of the parts of the formula that you're working through instead of just the result or end result so we'll look at some of that in future presentations.