Lessons

Lesson Info

Cell Referencing & Formulas

At this point what I want to do this is kind of shift gears a little bit and let's talk about cell referencing and self formulas so this is really where a lot of the magic happens in numbers um I mentioned before just one thing I want o reiterate is the find and replace when you're working with large sets of data if you want to do a finder replace their two ways to do that one that I showed you earlier is simply coming up here to this this button at the top and choosing the find and replace option another option is if you do command f so let's go out and do that that will bring up let's close this by default that will bring up this find and replace window this is really good if you're just looking for a specific word so in this case let's look for tim okay? And I can see that we have one cell found with the name tim in there if there were more than that we can use these arrow buttons here to move back and forth between between those so that's really useful if you have a lot of data if ...

you want to switch this to a find and replace scenario so we actually want to find him in the replace it with something we select the drop down gear and choose the find and replace feature so at that point it's still going to love for tim and now we want to replace this with timothy because now he goes by timothy ok and then here we can do a replace all that is going to find all instances of tim and replace it with timothy as you can see that that's happened right there so it's a really nice future um okay, so building up to formulas and referencing one other thing that I wanted to show you I'm going to add a new sheet here and let's go ahead and add more rose to this I have let's say hypothetically I am creating a product sheet and my product numbers are one, two, three, four, five, six all the way up to seventy okay, so there's a couple ways that I could do that one is I could go one, two, three, four becomes tedious really fast another way of doing that. So let's, go ahead and delete the's is when you hover over a cell, you'll notice a little yellow dot actually, you'll notice two dots let me talk about the white one first to the white one is really simple that allows you to expand your selection area guess if you want to select more area for that selection, the little yellow one allows you to copy and paste and payson payson payson paced the contents of your selection so if I were to highlight this cell and use that yellow dot and drag it down I am copying and pasting and pacing and pasting the value of one all the way down ok that's pretty cool there are other ways of doing that but that's pretty cool um something else we can do and this is where it gets interesting is if we do a one in the first cell and two in the next and a three in the next and then we highlight all three of those and we pulled down on that yellow dot look at that so what's happening in the background is numbers is smart enough to know that wait a second you've got thes three selected I can see that there's a pattern here one two three I'm smart enough to know that the next value is for than five than six and it's going to do that for us automatically you can even do that with letters so if we do a b c it's going to highlight those so that's pretty cool if you're doing something alphabetical one more little twist on that is if we want a repetitive pattern of numbers and letters so let's say we do a b a b I can't think of a scenario off the top of my head why you would do this but let's say you wanted it to go a b a b all the way down if you highlight that and pull down on that it's going to repeat that sequence of events because it's smart enough to know you've got a pattern you're trying to put into place and it's going to repeat that pattern ok perfect so let's move on so there are a couple of ways that we can add formulas there's the easy way and there's the hard way the hard way is we know how formulas work we know the structure we know the components that make up a formula and we hard code our formula. So for example, if I wanted to take an average of megan's test scores on the first semester second, third, fourth and final I could dio equals and by the way, every formula has to start with an equal sign. Okay, that's what tells numbers? Hey, numbers here comes a formula. Okay, so it starts with vehicles and that triggers this this new pop up window here so we could dio equals. And then the keyword to do an average is a v g where you could actually type out average so a v g lips click on average because that's what we want and then the values now when we talk about cell referencing, the eighty two percent that megan got on her first test score is in a cell. That cell is referenced by the location of b two right so we look at the column and we look at the row and we look at the intersection of those and that's how we reference what that cell is to be two would be herself so in this case I would say be two through f two I don't even like doing it this way any way you could that's kind of the point that I'm making you could hard code your formulas but in the end it's more work on your part it doesn't make sense to really understand how former those work necessarily at that level so let's do it the easy way is that okay? Okay prefer that yeah exactly so we're going to click on the x here that means we're not going to commit that basically canceling out of that okay so what we want to do is we want to take an average so we're going to do equals again because we need to tell it it's a formula we'll going to start typing average because we still want to take an average now at this point it's looking for a value and we can see that because values listed here as the parameter so at this point what we're going to do is highlight the first semester grade come all the way down to the final grade and you'll see how it's done the hard work for me so I actually had the colon right? It just wasn't reading that properly so in this case, we're going to take the average of b two through f two, which is her grades, including the final when we're done, we can either hit enter or we can make we can commit using the green check box, and that is her average score. So at this point, and by the way, we can apply conditional formatting on formulas as well, so for average score isn't up to snuff in it, and it turns that yellow. Maybe we can address that and dig deeper and find the score that resulted in that lower average score. Um, so at that point, we can, you know, we're good we got her average score if she happened to retake a test, and maybe she got one hundred percent on the seventy eight percent obviously, formulas are real time, so we would adjust accordingly when we're done with that. We can now copy that all the way down to the other students in the class by using that same yellow dot of copy and paste. Pace, pace, pace, pace. Now throw individually. It won't. Yeah. So if we and that's a good question, if you if you highlight one of those cells you can see visually numbers is really good about telling you what what's going in to make up that number so if we highlight this one down here, we can see we can just confirm that these numbers were going into that. Now these numbers on the side here are only live taking an average of that ro in addition to that, we can then take and make another average of that column to have an overall class for the year average doesn't make sense does that? Does that answer your question? So we did it going horizontally? We can also do it do it vertically so we can take an average score of each semester test grade and then again, we could do an overall overall average. So that's one way of doing that, I'm going to show you even ease your way of doing simple formulas. So what I'm going to do is actually the river's I'm not going to select this on equals first, I'm going to highlight the numbers that I want to take an average of, and you'll notice that the bottom something's up here so I can see by highlighting these five cells that the average score is eighty nine percent the some score which doesn't make sense in this case is four hundred forty five percent, the minimum is eighty two, the max is one hundred and there are five test scores that go into making up that number that's really cool because of two things one if I'm doing a quick analysis on something, I can actually highlight the entire class and just by doing that I can see that the average scores eighty eight point four two percent without doing anything else I don't have to add that in is a calculation I don't have to do anything with it but that's a very quick way of calculating numbers now obviously it's not doing it for all types of formulas but it doesn't for the five most common in a case like that and I can see that there were fifty five test taken if I like that number so let's go back up to megan's will highlight the's her average is eighty nine percent if I like that number and I actually want to add that to a cell I can take this area down here and drag and drop right there and it's it's it's done everything for us that supercool to really useful yeah so just highlight because that's the way that a lot of people think a lot of people think in terms of here's my data then what do I do with it? Not this where I wanted to go let's go backwards and find the data so for me it makes more sense it's more intuitive to select what I'm looking at and then you know it looks good dragon drop so that down it will give you the individual exactly. Because in the end, whether we hard coded whether we started with equals and then we dragged that way or we do it this way, the end result in that cell is exactly the same. So from here, we can select that sell like that and dragged drop it's gonna copy that same formula down. So again, this goes back to you. This makes it very, very quick to analyze your data. I mean, just being able to take an average what's that we need this when I had my office. Well, now you know yeah, yeah. It's a it's, a very nice feature. So those air really simple formulas let's talk about a little more complex formulas, and we're not going to go into a lot of detail on this, but let's, go ahead and add so one of the reasons why you wantto maybe keep the function, but nor the formula button on their tool bar is for some more complicated formulas. So if we highlight the cell that we want the formula to be targeted to, and then we click on the formula button so you see these right here, these look familiar to us, we're going to go down to create a formula and that's going to open up a new functions pain on the side so here this looks familiar to you already case were from there with that on the right hand side this is a list of all of the formulas that come preloaded in numbers and this is pretty, pretty typical pretty standard a lot of the same formulas existing excel and other sprays she applications they might very a little bit but it's all from those are formulas they don't change right? So for example, we can come down and find something this way we can look by category, so if you're looking for something financially, these are all the financial formulas. If you're looking for something new miracle these air all the numerical formula so they haven't broken down or if you're looking for something specific so let's do a search on mortgage we want to we want to create a special on mortgage payments so let's do a search for mortgage so these are all of the formulas that have something to do with the mortgage. Obviously they're a little bit different on dh from here if we want to find the right one, what we do is we highlight one and we don't have to use it at this point, but if the bottom we can read all about this, we can read what it does how it's used what parameters it's going to be looking for so in in this example, if we scroll down this gives you a lot of good information it will actually give you some examples and it will give you let's go back up it will give you so this one is looking for a periodic rate a period number of periods present value future value and when it's due so those are all the parameters that you will need toe have in order for this four minutes work so if you don't have all those all those all those properties maybe rethink that until you have that then you can apply the formula has got some really good information about the formulas so formulas wow, right that's pretty cool stuff there could probably be an entire class just on formulas diving into all the things that absolutely there really could be I mean, you saw the list of formulas and it could go on forever we could spend three days talking about this so you guys have time? Ok let's go on to sell referencing and we'll kind of wrap up this lesson so sell referencing is is important to understand because this is when we want tio look at data from one table that is located in another table so in this example what I want to do is let's go ahead and create this average here so we're gonna do with the quick way let's copy that down all right perfect so here we have our detailed data and we have an average for each test score ok, so what I'm going to do is come over a sheet too and at this point what I'm going to dio is I want a nice clean summary sheet that maybe I can print out and post somewhere maybe for the students to see maybe for you know the principal to see whatever um but they don't need to see all the detail here so what I'm going to dio is I'm going tio start here and when I talk about self referencing cell referencing is a type of formula so we're going to start with the equal sign ok, so we're gonna do equals and then we're going to switch over to sheet one and we're going to select the meghan field here and we're going to hit enter ok, so it looks like no big deal, right? Megan on both sheets no big deal but the cool thing is if I come back over and we happen to change her name to julie guess what happens because this cell is just looking at the other cell for that data so like you would expect we can do this and that's picking up all of those because what's happening is if we look at the if we look at the format on this one well let's look at julie again sorry let's, look at julie again so it's looking at test scores, which is the name of that table in the other sheet sell a to the next one is looking at a three a four etcetera, so we're just copying and pasting that and it's smart up. No, they were copying down it's going to reference down as well is going to adjust that for us, so we've got that we also want to bring in the right, so average scores and let's go ahead and type equals here, go back to shoot one and let's click on that one and hate okay, bring that down. So now we have their average scores, so this would be a great time now to just print this or or take a screen, grab or do something with this, and send it to the principal, or send it to someone else who can get a nice summary of the data again. They don't want all of the data, but it's done in a way that if if this is still a working document, if maybe a couple of these students we allowed them to retake the test, we can go in and change the values. Not only does it update the value on the right hand side here, but because the other table is just referencing this one it's going to update the entire the entire flow. So so that is sell referencing were your thoughts that gets you excited to go back and do some new, interesting things with with your business and some numbers files? What I like is that you can take, like, just a set of data sales data, inventory, whatever it happens to be, put that data in one sheet and then use the other sheets to analyze it, organize it, you know, display it in different properties, we only have to enter it once. So it's, so convenient to just have the data there and then work with it. Absolutely. And in this example, here, this this may not be a really good example for what we're talking about here, because it's a small set of data and it's, a limited number of values that we could easily visualize and easily adjust and manipulate. I worked in a situation once where we had a piece of machinery that every every tenth of a second it was capturing specific data about that equipment every tenth of a second, and this thing would run for minutes and sometimes tens of minutes at a time. So you can imagine every second you know, that goes by that's ten rows of data for one second, so imagine sixty rose or sixty times ten rows, so that data could get enormous really, really fast. So this is just a easy way. Tio. Kind of visualize your data and organize it in a way that that is still flexible.