Hey, can you sum up S jobs, sales not So why do we have to do that for Excel? Why can't we say, Hey, sum up S jobs and have it? No. In fact, we can, this is called named ranges and to create them, we're actually going to need to dive into the formulas tab. Go ahead and join me here, inside the formulas tab. And we're going to find the defined names command group. Now the defines names command group is here because in most instances named.
Ranges are for formulas and functions. In fact, that's where they come in handy the most, and that's where we'll be using them the most. So keep that in mind when you're trying to remember where the defined name's command group is, where do you use them inside formulas and functions? So you'll find them in the formulas tab. Well, we'd like to do is tell XL that these four cells are S jobs sales. So go ahead and highlight from . And we're going to use the define name.
Button, which allows us to define the name of a selected field of cells. Give it a click and check it out. It's automatically guests that we're going to name it as jobs given the fact that it was the cell with text immediately to the left. Now it's not always going to guess correctly. So do make sure that you double check that before you click. Okay. However, you don't have to use that name. You can always type in your. It is important to remember. However, that spaces do not play nicely inside of named ranges..
Notice that even in Excel it's placed in some underscores, the next thing we're going to need to keep in mind is the scope. Where are we going to be using this name range by default, it's going to expand this to the entire workbook. However, we can only limit named ranges to specific worksheets instead. However, for the purposes of this class, we're going to leave it as workbook. You do have the option of putting in comments if you'd like, but in this case, I'm going to go ahead and leave it blank. It is important to note, however that these can be incredibly useful,.
So now I can place my cursor inside the totals field and just like with any function or formula, I'm going to do my equals sum open parentheses. But this time, instead of selecting the cells of the range, I'm going to go ahead and press the And check it out. There's S jobs, go ahead and find S jobs and double click to add them to the list. You'll see that S jobs is now available by closing out the parentheses. I can complete the SEM and by pressing enter, you'll see. Now it's taken all four cells that I told were S jobs and it's summed them up..
There's my function. Pretty cool. Go ahead and pause the video to catch up to me here. Once again, the way we created that was by highlighting the cells we'd like to name navigating up to the formulas tab and using the define name tool to define the name as S jobs. From there, we simply in the totals field typed N equals sum open parentheses S jobs. Go ahead and try that for yourself and then come back. Welcome back. So you've gotten your first taste of named ranges, but I can hear your concern..
Some of you might've tried to auto-fill down and you'll notice that when we did it simply replicated some S jobs. Wasn't quite what we hoped it would be. It's important to note that this isn't necessarily a real-world use case. This is simply an introduction into what named ranges are. Rest assured. There are plenty of real-world use cases. We'll be going over in the next couple of lessons. Another concern that tends to pop up during these lessons is there are five other individuals here. Do I really have to go in row by row to define these names?.
The answer is yes and no. Yes. If you want to, however, no, if that feels like a waste of time, if you'd like to get this over with and believe me, I would too. We're going to go ahead and highlight the remaining values starting this time by including the sales person's name. So I'm going to click and drag from IE Musk all the way down to the final sale value of $10,252 this time, instead of using the defined name tool here in the formulas tab, I'm instead going to find the create from selection button..
It's important to note that this tool only works. If there are row labels or headings, and this case here, I'm going to go ahead and click create from selection and check it out. Where are the names that it's going to create from? Are they on the top row? Not so likely. However, they are in the left column. IE Musk w buffet, M Mayer S Sandberg and solar. So those are our names. Let's go ahead and click. Okay..
Posts Related:
Yet again at first glance, it doesn't look like a lot's happened, but let's take a look and see just like in the last process, I'm going to go
Ahead and start by calculating III. Musk's total sales. I'm going to start with my equal sign. Now I can use my F three key and pick the name from the list because check it out. Hey, that create from selection created all of them in two clicks, but I can also use the using formula dropdown available in the formulas tab..By clicking on that, I can choose another name, grain from the list for those of you with computers that don't have an, a functional F three key, you can always use the paste names field from this as well. And that'll pop up the pace named box. Either one will work in this case here. I'm going to go ahead and select from the list. IE Musk. Don't forget my son close out the parentheses and there we go..
So go ahead and take this opportunity to catch up here and then calculate the remaining values for w buffet M Mayer S Sandberg, and a horrendous. Go ahead and pause the video and catch up. Welcome back. It's already, hopefully you're starting to see that creating named ranges is easy, a little too easy, really. And in fact, because it's so powerful that you're going to increasingly see its value as we go through this process. The next thing we're going to need to do is calculate the total sales and average sales. Now, in order to calculate those, of course, we can go through a.
Standard process, but for the sake of practice, we're going to use named ranges yet again, to sum up total sales and average all sales. So go ahead and highlight everything in the totals column from S jobs down to a horrendous. And once you've done that inside the formulas tab, go ahead and use the define name button to calculate February totals. This time. I'm going to go ahead and add on to this already pre-filled name. So now I've got my February totals. I'm going to go ahead and click, okay. To lock that in..
And now I'm also going to go ahead and highlight everything from week one to week four, having highlighted that there's not really any place for me to create from selection because this time there is no header. So I'm definitely going to have to define a name here. And in this case, I'm going to go ahead and call this all sales and click. Okay. Now that I've created those two named ranges, I can now calculate the total and average sales here in total sales I'll type equals sum, and then using either the.
F three key or the using formula dropdown. I'm going to select February totals and close out the parentheses. And likewise, I'm also going to, in the average sales field type, my equals average open parentheses, and then select all sales from the use and formula dropdown, close out the parentheses and our lock it in another benefit of having used these name ranges is that now these formulas are practically in English, meaning that I can glance at any one formula and find out exactly what totals I use, what numbers I used, where are these data points are coming from?.
Go ahead and pause the video to catch up with me here. Once again, we created two named ranges, the total's name range, which we then used to sum up total sales and the all sales named range, which we used to calculate the average everyday sales. Go ahead and catch up to me and then join me after the pause. So that's your first taste of named ranges. Don't forget it. Rest assured that this is coming back very, very soon. In the meantime, let's shift gears. We've calculated all the total sales..
In fact, we've calculated the total sales and average sales for all of February as well. Well, we'd like to do now is complete the rest of the dataset. Now, currently it's asking was the goal met. And as I look around, I see there is a monthly goal of $34,000. So let's take a look in regards to S jobs as jobs sold $36,245 compared to a monthly goal of 34,000. I think it's readily apparent that S jobs did in fact meet the goal..
So I guess I just type yes. Right? We did it. What's the problem with this? Why can't I just type? Yes. Well, the problem is I didn't tell Excel how I knew that the goal was met. I just told it that it was the problem with that is let's say I turn this into a template and use it next month for March, 2015 sales. Well, if the numbers update and let's say S jobs doesn't do as well. And the sales drop below 34,000, that cell is going to continue to say yes..
So it's not necessarily doing exactly what I hoped it would do, but I thought Excel was smart.
It should've known. Not yet. We've got to tell Excel how we got there and how we got there was by using logic. So let's teach Excel the logic we used in this example, we're asking a question, why is this cell greater than this cell? Did the total beat the goal in order to ask that question to Excel, we have to pause it in a very specific way and a very simplistic way..Basically we have to ask is something true or not. And to do that, we have the, if function, the function allows us to ask Xcel a question. And depending on the response respond accordingly with either if true, do something or if false and do something else. So let's go ahead and type our first, if function. Now I know many of you who already worked with functions are comfortable typing these functions by typing equals if, but you've actually been doing.
It the hard way, not the wrong way. And in fact, if after this lesson, you decided to continue doing this the exact same way you've always done. That's fine. But I like to show you guys a new way to inject functions, especially complex functions into Excel. Placing your cursor in cell H five. We're going to go ahead and find the insert functions tool. This can be found to the left of the formulas. By clicking on the insert functions button. You'll see a little search field. I'm going to go ahead and search for if and once I've typed, if I'm going to click.
Go, this is an actual library of every single function inside of Excel, by typing into the search function and clicking go, I can bubble if up to the top. And on top of that, I actually get a description of what it actually does and where better to get a description. Then from the people who actually wrote the code. In this case, Microsoft describes if, as checking whether a condition is met and then returning one value of true and another value, if false makes sense to me, once you find the function, go ahead and click, okay..
Which will bring you into the function arguments box. The function arguments box exists for every single function inside of Excel. Even the simplest ones, like some, what it does is it compartmentalize compartmentalizes, each individual part of the function into its own little box. This allows you to focus on one individual part of the function at a time allowing for the utmost focus and control as we go through this here, you'll see the benefits. The first thing it's asking for is the logical test. Now, if you're not entirely sure what the logical test does, you can always look down here at the bottom, no matter where you are..
If you click inside the box, It'll tell you what that box is asking for in this case, the logical test is any value or expression that can be evaluated to true or false. This is our question in this case, the question is, is the total cell greater than the monthly goal. So let's go ahead and take that into account. I'm going to just click on the total cell. So F five, was it greater than or equal to I too? And interestingly enough, because we're inside the function arguments box, it's actually automatically answered that question in this.
Case, F five is greater than I two. So we know because it says true that our question is right. The next thing it's asking for is the value. If true field, what do we want to Excel to do? If the cell is true? If the question is true, we probably want the box to say something like, yes. So let's go ahead and type. Yes. However, it's important to remember that you can't just type text into a function. You have to remember that if you're typing an actual text field, that's not a function. You need to surround it with quotations..
Once you've done that, go ahead and tab down to the next field. So if the question is true, the box should say yes. However, if the question is false, What do we want the box to say now? Usually the response is no, but let's have some fun with it, right? How about not a chance. Notice I remembered to put quotations in there. Now what's the scariest part about building a function for those of you.
That have built them in the past, you might answer that the scariest part about building a function is remembering all the commas and the parentheses. However, the function arguments box, you don't have to remember the commas or the parentheses. This box takes care of it for you. In fact, take a look at the formula bar. This is the function we just built. Notice it took the commas and the parentheses into account already. Nice, right on top of that, before we even click, okay, we can even see whether.
Or not this function is going to work. So no more holding your breath before you tap the enter key, simply look in the bottom left corner by the formula result, and you'll see exactly what the cell output will be. Now, of course, if you're seeing an error here, you should double check to make sure that your boxes match mine. Once we've asked the question, we've got our value of true and our value of false responses. Let's go ahead and click. Okay. And there you go..
Was the goal met. In this case, the answer is yes, 36,245 is greater than the monthly goal of 34,000. Go ahead and pause the video to catch up once again, the way we got there was by using the insert function arguments box, which can be found in the formulas tab. On the far left hand side, this tool can also be found here to the left of the formula bar. These are the same tool. One is just a little bit more accessible than the other. Once you opened the function arguments box, simply make the changes that you need to ask the question, input, your responsive,.
True, your value of false and click. Okay, go ahead and pause the video and catch up. All right. We'll come back. So at this stage, we've asked a single question. Did S jobs meet the monthly goal in this case? The answer was yes. Now none of us want to have to rebuild the same function over and over and over again. Thankfully, of course we have auto-fill so let's go ahead and take a look at that by placing our cursor on the bottom right corner of the active cell that has the formula we'd like to copy. We're simply going to ensure that we've got that thin black cross click.
And hold and drag until we get to the bottom of this box and then release. Now at first glance, it looks like it works, but of course, as any good mathematicians will tell you never trust your own. So let's take a look, go ahead and place your cursor on the first cell, the one that we built. Okay. So we've got 36,000. That's greater than 34,000. Good. Uh, 31,000 greater than 34,000. No, it's not, but what's going on here?.
Why does it say yes. In fact, as I'm going through this year, I'm seeing in a couple of instances, it's saying yes, when it should not. And this case here, unfortunately, as Sandberg did not do what we hoped they would do move missed it by a dollar, but it still says yes. Now either the computer's being generous or something's gone wrong. In any case, we're going to need to take a deeper look at this. And for that, we're going to need help to analyze all of our formulas that we built. We're going to need access to some of these formula auditing tools..
Let's go ahead and place our cursor on the first cell. Now this is the cell that we built. So we'll use this as a test run to make sure that we know everything's working. Go ahead and find the trace precedence button and give it a click and check it out. This is a visual indication of where this cell and its function are pulling reference data. In this case, this cell is referencing the total field and the monthly goal field. Okay. Everything looks good here. I'm going to go ahead and go down to the next cell. Yeah..
And once again, we're going to trace precedence, huh? That's a weird, when we built the function originally, we were asking, does the total exceed the monthly goal? And why is it then when I trace precedents here, it's asking, does the total be nothing? Because of course the answer is yes for that. Let's take a look at the cell below. And once again, we're going to trace precedence and now we're starting to see the problem. When we auto fell down, we didn't remember to absolute reference the cell for those.
Of you who aren't familiar with, absolute referencing refer back to earlier lessons. But for those of you who are what sell in this formula, shouldn't have changed if you said I too, you'd be absolutely right. Each total cell should be referencing 34,000, no matter what. And in fact, if we click on the show, formulas button, first things first, let's get rid of these arrows, go ahead and select remove arrows and Lao let's show formulas. This is going to reveal all formulas inside the worksheet..
So as we're going through this year, we see that when we autofill, because we didn't absolute reference. F five became F six, became a seven, which is right, because we wanted to continue referencing each total one row after the other. However, I too should never have become , which became I four. And so on. I too should have always stayed. I too. So we have to fix that to fix that. Go ahead and place your cursor either on cell H five and double click or click.
On the cell and in the formula bar, go ahead and place your cursor on top of ITU. Now it's a small change, so we don't need to do anything too crazy. We're simply going to place our cursor on cell I, to the cell that shouldn't have changed in the first place. And we're going to absolute reference it using our keyboard shortcut of F for once again, noticing that when you tap the F four key, we now have dollar signs on either side of the column value and the role label..
We've now absolute reference to the cell, meaning that when we auto-fill, Xcel knows that I too should always be I too. Go ahead and press enter to lock that in. We're now going to auto-fill this down to ensure that this works the way we expect it to by clicking on cell H five, we're down, going to auto-fill down all the way to H. Once again, turning my fat white cross into a thin black cross. I'm going to click and drag down to the very bottom and there we go..
So now we see F five to I, to F six, to I, to F seven F eight. Everything is now working the way it should. Let's go ahead and show formulas, simply click the show formulas button once again, to resolve it back to its original view. Let's double check. 36,000 is greater than 34. So yes, 31. Not greater than 34. So not a chance. Okay. 33. Good. All right. So it looks like by absolute referencing cell, I too, we managed to salvage.
The function and we didn't have to build all the rest of them ourselves. We allowed autofill to do that, like work for us. Pretty good. So it's nice to see that we have some auditing tools that allow us to evaluate functions and formulas to ensure that they're all working properly. And when they're not these tools vastly come in handy when it comes to making sure that we know where and how to fix these tools, when the time comes, go ahead and take this opportunity to catch up. Once again, verify that your, if functions are all answering the question properly, if they're not use the formula auditing tools, both trace.
Precedents and the show formulas. In order to evaluate whether or not they're referencing the appropriate cells, pause the video and join me after the break. Welcome back. So we've asked our first question was the goal met and with a little bit of work, we got excelled to answer that question for us now, what do we want it to do? What we probably want it to calculate the bonus status. Let's say for example, that if they hit their bonus, they get a 2% commission.
On top of their regular paycheck. Now, if I just go into the cell right here and calculate 2%, let's say I do that. And so I have five we'll just do equals the total times 2%. Okay. $725. I auto filled down done. Right? Except that only the people who hit the goal should get the bonus. So what am I going to do manually delete the cells that have bonuses where they shouldn't or can I let Excel do that for me?.
If you followed along, go ahead and clear out your bonus status column, instead of just typing out the answer we're instead going to give Excel the power to logically determine whether or not they should even calculate the bonus. Once again, we're going to start by inserting our, if function, we saw that we were able to insert our, if function using the insert functions tool, either. Or here inside the formulas tab this time. Let me show you another way. This time using one of the function library, dropdowns, the function library, dropdowns are simply books that contain specific functions..
The function would be considered an a logical function simply because we're asking a question. So by clicking on the logical dropdown, you'll see there's if, and I'm going to give it a click. So once again, we're going to answer the question was the goal met this time, however, instead of asking the question by calculating one cell greater than, or equal to another, let's calculate by asking whether or not this cell says, yes, oops, what's going on here?.
Why did it give me an error? I'll remember what I said. When you're putting text into a function, you've got to remember to put quotes around the text. Otherwise Excel is going to try and read that as a function and there is no yes. Function. So H five must equal. Yes. And check it out. It's true. H five doesn't vaccinate. Now, what do we want to Excel to do? If the is true last time we just had it say yes, but in this case, instead of having to say yes, how about we have it calculate the bonus.
For us in this instance, the total sell multiplied by 2%, the same function we use before we'll tab down. And what do we want the cell to say, if false, how about a big fat goose egg in this case here, we're pretty much done, but we do want to verify does age five, say yes, yes it does. So if it's true, it should calculate the total times 2%, which comes out to $724 and 90 cents. Meaning that because age five does say yes, I should have a formula result.
That says, boom, $724 and 90 cents. Now that we verified left click, okay. To lock it in. And there you go. We can now auto-fill this down. And in theory, it should cross-reference to see whether or not the cell says yes, if it does, it should calculate the bonus. If it does not, we should see zero next to not a chance. So I'm going to go ahead and auto-fill down and moment of truth. All right..