For module two we have another set of objectives. We'll be taking a look at some useful array functions. We'll take a look at advanced conditional formatting. We can create our own conditional formatting based on formulas. We'll also take a look at advanced charts. Then we'll finish up by creating some macros by turning on the developer mode. So go ahead and open up the practice file for module one and come right back and we'll dive in. I've opened up my power user module one file here. And let's go ahead and take a look at our very first worksheet..
So our very first worksheet will be covering the if function. And the f function is basically the foundation for our logical functions. So in preparation for our database functions we want to make sure that we understand the f function. So for our functions we're going to be inserting the function. You can either type the function here in the cell. We can also type it in the formula bar if we want to. But we're going to be inserting it the traditional way from the formulas tab.
And clicking on the insert function dialog here as well. We can also press Shift plus F3 to get that done. Now, in order to understand how this works, let's take a look at the F function and this most basic form. In this case we have some sales figures for the month of February. A few sales representatives here. For example, for example, we're tracking the weekly sales week one 9550. Week two 9230. Week three 8500 and week four 8965..
So in the end, the totals for XM for the month of February is 36,245. So the question is, did Asam meet the monthly goal or the monthly goals over here, which is 34,000? So if we were to compare the value in cell E5 and I12, we would say something like, well, is the value in a five? Greater than or equal to the value that is. Am I 12?.
And I'm going to insert my function. So I'll choose h5 here. I'll go ahead and click on the formulas tab. I'll go ahead and insert my function. We can also press shift F3. I'll go ahead and type the f function at the top here. I'll press go. Then I'll press okay. Here's my function arguments dialog box tells us what the function is, what the function does,.
And for each argument what is actually asking us for. So we can see that this function needs three things. We need to introduce our logical test. Optionally, we can specify what should be displayed in the cell if the value is true or if it's false. Notice how these are not bold as the first argument. So these two are optional because by default they're going to return either true or false. Well let's go ahead and set this up..
So our logical test we're concerned about two cells. We're concerned about cell F five that has a sample cell. We want to see if it's greater than or equal to the value that is in i12. Excel has already determined that the value here is greater than the value that's in here. Therefore, it's already telling us that it's true, but we don't want it to say true. So if it's true, we'll just simply say yes in quotes..
If it's false. Or simply say no. Here's our answer. It's a really telling us it's going to be a yes. I'll go ahead and press. Okay. And we should be good to go. So there it is. We have our yes. Now we want to be able to autofill down. So if we grab our fill handle here, we can fill down and answer the rest of the questions here as well. So what I want to do is I want to go ahead and absolute reference.
My monthly goal that is in cell I12. So to do that I'm going to rewrite it this way. It's going to be F5. Is it greater than or equal to dollar sign I dollar sign 12. So this way when we write our formula. The values in column F will move down row by row. But we're going to go ahead and lock on to the monthly goal. So I can just go back to my original formula here in the formula bar. Or if I double click on the cell I can see the formula and I can go ahead.
Posts Related:
- How To Add An Onboarding Screen To Your Blogger Website - Live Blogger BlogSpot
- 4 steps Infographics - **FREE DOWNLOAD** Powerpoint template - Presentation
- Project Beginner Tutorial
And right here I can just go ahead and press the F4 key on my keyboard. And that will absolute reference the i12 for me.
So I'll press F4. There we go. And now I can just go ahead and press enter. I'm good to go. If I autofill down. Now as we take a look, if I double click on that second cell, I can see it's moving down in column F, but it's still locking on to I12. If I double click on the next one here,.Moving down as expected in column F, but we're still locking on to I12. So go ahead and write your if function for column H. Answer the question was the gold map. You should get three no's and three cells with a yes. And come right back. Now that we've answered the question, was the call met for each of our sales reps? Let's move on to column I..
We're going to go ahead and calculate the bonus. If our sales rep met the monthly goal. So we do have a bonus opportunity here of 6% of the sales. If the goal was met. So in this case, as Sam did meet the goal. So what we're going to do is run the calculation. We're going to multiply the value that is and sell a five times 6%. So we're simply going to say if the value was true.
We'll say five times the value that is in either 13. We can use the same statement as before. So for example we could use our same conditional test here. If the value of five is greater than or equal to i12. Instead of saying yes though, this is where we're going to perform our calculation. So instead of saying yes, we're going to perform our calculation, we'll say F5. Times the value that is an I 13..
So that's the only change that we'd need to make here as well. So let's go ahead and set this up. We'll go ahead and click on cell E5. I'll go ahead and insert my if function again that's already up here. So I'll just go ahead and press okay. Again I can use the same test. I could have even copied the formula here as well, but I'll go ahead and write this over. So F5 is a greater than or equal to the value that is in I 12..
Since I'm right here, I want to go ahead and highlight the i12 and press the F4 key on my keyboard. That will absolute reference that for me. Now the value if it's true. Remember we want to perform a calculation. So I'll multiply the value in F5 times the value that is in I 13. I am going to absolute reference I 13 because I want to be able to fill down my formula. So I'll press the F4 key on my keyboard again. And there we go. We can see it's already giving us the calculation because this scenario is true..
It's already performed the calculation for us. So we should be getting a 2175 bonus for SRM. If it's false, I'll just say something like Na and quotes and we're good to go. So just to recap, our logical test is the same as before. It's the value in cell F5 greater than or equal to the value that is in IE 12. So in this case, because it's true we're performing the calculation. I'll go ahead and press okay. And now I can go ahead and autofill down because I have values to the left..
I can double click on the fill handle here and it will copy it down for me. So there you go. I'm good to go with my f function here. So the f function we can either say true or false. We can have our custom text or we can perform a calculation as well. Now this is dynamic. So if I were to give Sandburg the extra dollar here to meet the monthly goal, I'll go ahead and change the value here. And notice how this will change to. Yes. And we'll also get the bonus that is updated right here as well..
So because we're using the cell reference everything is dynamic. So now all we need to do is just input values. Here or actually over here. So for each month as these cells are populated these two columns will be updated automatically as well. I'll give Sandburg the extra dollar. So 34,000. I'll press enter. And there we go. Right. Go ahead and write your second function to calculate the bonus status.
And come right back. Let's go ahead and write one more function to complete this worksheet here.
So we have one more opportunity here in cell K5. So even though you did not meet the monthly goal, we still have an opportunity for a department bonus of one k. So everyone gets one k. But it's based on two conditions. So the first condition is that the total sales needs to be greater than or equal to 200,000. And then the average sales here.Needs to be greater than or equal to 40,000. So we can see that one is true. So where do you 213. So this is true. However this is false. So we'll need to go ahead and write a function to determine whether both conditions or true. The keyword here is and there's actually two ways we can do this. We can either write a single and function. The end function will just give us the default true or false..
If we want to go ahead and write a custom message. However, such as, you know, great job, team or keep trying, we would need to nest the end function inside of our if function. Well, let's go ahead and write our end function first, and then we'll just go ahead and nest that in our if function. So we're using the end function because the f function only allows for one logical test. Right. So if I open up my if statement here I can see it only allows for one logical test..
But what about the end function? I'll click on the insert function here. I'll go ahead and insert my end function. I'll press go and I'll press okay. So here's my end function. And as you can see it provides opportunities for more than one logical test. We can actually go up to 255 logical tests as well. Well let's go ahead and enter this as our first logical test. We're concerned about the value that is in cell F12. Is that greater than or equal to the value that is in.
L 12. So 213,000 is greater than or equal to 200,000. So this first part is true. Now a logical test to. I'll go ahead and click here is concerned with the average sales. So it's that greater than or equal to the value that is in cell L 14. So we can see that that is not the case..
35,602 is not greater than or equal to 40,000. So we end up having one that's true. And one that's false not the end function. Both need to be true. So because one is false we end up with false here as well. So if I press okay this gives me a simple answer letting me know that well it's false right. We're not going to get that department bonus of one k..
But what if we wanted to write something custom in here, such as? You know, maybe if. The department gets a bonus, we could say something like great job, team. Or we can just say something like, keep trying as a department. Well, to do that we would need to nest the F function. So look what I'm going to do. I already have my and function here. That's giving me the result. So I'll just go ahead and copy my end function..
So I'll copy that to the clipboard. I'll press escape to get away from my formula bar here. And maybe over here. Create a little space for my function. So I'm going to insert the f function. Okay. So my f function is only concerned with either a true or false value. Well remember my end function that I just wrote is giving me either true or false..
So I want to go ahead and copy and paste the end function in here. Right. So my logical test as a result of my end function now we can see that it's giving me false. So mission accomplished. So now if it's true I can say great job team. And if it's false and you know we can say keep trying. As you can see in this case our end function is returning false for us.
Because both conditions are not met. So therefore the result will be to keep trying. Now one of the cool things here, now that we have the function arguments box open to simplify things, what I can do, take a look at the formula bar is I can go back and forth between the if function arguments box and the end function arguments box. So if I click on the end function here, notice how it's switched over to my hand function. So this is just a little.
Tip helps us when we're nesting functions here. Now I can toggle back and forth between F and M. I'm good to go. I'll go ahead and press okay. There is my answer. Now remember all of this is dynamically linked. So if these values were to change, once we get to 40,000 average sales or more, our message here will be updated as well. Go ahead and write your hand function and go ahead and nest your ad function inside of your F function as well, and come right back..
On this worksheet, let's take a look at several variations of the F function. In particular, counter some f average f and some f's. Let's take a look at our expense report. We have some expenses from rows six down to row 50. If I scroll down it goes down to row 50. Here. So we have the date of the expense. We have the office either uptown, Midtown or downtown. We also have the actual expense. So technical support, communication, office supplies and transportation and so forth..
We're also tracking the amount. So for example, on July 1st we had our first expense. It was for the Uptown office for technical support and the amount of $747. The first thing we want to do is we want to go ahead and highlight all of our tech supports here as well. So how many expenses did we have for tech support? Well here's one. And if we go down here, here's two and here's three.
And so forth and so on. So what we're actually doing is we're taking this input value here. So we want to count how many expenses did we have for tech support. We're taking this value and we're searching the expense range. And we're counting how many times tech support actually appears in there. So if you think about it, what we're doing is we need a range..
We need a criteria. And that's it. We already have that information. So the range that we want to search well that range is from C6. Down to C 50. And our criteria well our criteria we can either type technical support there or we can refer to cell G10. We want to use the cell reference because if we change tech support to another expense we can get a real time.
Update in terms of how many times that expense was logged as well. But the first thing we want to do, let's go ahead and name our ranges. So instead of highlighting, you know, C6 all the way down to C C 50, every time we want to run some type of function on here, let's go ahead and name the range. I'm going to go ahead and highlight the entire range. And right up here in the name box I'm going to go ahead and give this a name. So for the office expense range I'll call this expense.
Or expenses. So notice I've highlighted the entire range in question. And I'm typing the name of the range right here in the name box. Well we'll go ahead and press enter. And now I have a range. I'll put the name right here so I can remember what it is. I call that range expenses. I'm going to do the same thing here. Well, now what I can do I'll go ahead and click on the dropdown..
And I can see I have some ranges in here. Notice we have some tail ranges. We'll take a look at this in just a little bit. If I click on that expenses range notice how it highlights this range for me. So from now on when I refer to expenses it's going to refer to C6 down to C 50. Well we'll see how this will help. For example if I want to go ahead and summary and maybe count amount of expenses in that range. Well now I can come in here and I could use the counter function..
So I can insert my counter function. And as I mentioned before it's going to ask you for the range and for the criteria. Well for the range I'm just going to type expenses in here. And notice how it's giving us a preview of the actual range. So tech support communication office supplies my criteria. I can either type technical support in here. And quotes. And it tells me that there were six entries for a tech support. But I want to use the cell reference instead..
So I'll go ahead and refer to cell G10. So I'll go ahead and just click on cell G10. And here. So whatever value we delete that. There you go. So my range is now expenses aka C six through C 50. My criteria is tech support aka cell G10. If I press okay. I'm notified right away that I have six expenses for tech support. Now I can change this to kind of give it a test..
Maybe I'll go ahead and check to see how many times did we have meal expenses logged. So I'll type meal in here and I'll press enter. And we had seven. So that is what the counter function does. But combining that with naming the ranges helps us to work more efficiently. Go ahead and name the office range. So B6 to be 50. Go ahead and name the expenses range C6 to C 50. And go ahead and name the amount range which will be d6 to D 50..
Go ahead and enter your counter function. Give it a test and come right back. Let's go ahead and answer our next question here. So what were the total expenses for office supplies? So in order to answer this question, we need to find all of our entries here for office supplies. Go ahead and locate the amount and the amount column and add that to the running total. So if we were to do this consistently. We would actually get our answer right..
So at least in my view right now here at least the very first three entries for office Supplies, we add these numbers together. And that is what the sum of function will do. So let's take a look at the sum if function. Now we already know we're going to be asked for the range and the criteria. We already have the information that we need. I'll go ahead and click on this cell for age 14. And I'll go ahead and insert the sum if function..
So here's some if and again what's the range. Well the range is going to be my expenses. So I'll type expenses in here. What's my criteria. Well it's office supplies but I want to use the cell reference here which is G14. And then the third one what is the range that I want to summarize. Well I want to summarize the match that's found in the amount column. I didn't name that as amount. So I'll go ahead and type a mountain here..
And there we go. It's already giving us our answer. The total for office supplies is 3902. All right. I'll go ahead and press okay. And there we go. All right. Just need to go ahead and give this the accounting or. Oh currency format. I'll give this currency and press okay. And there's our number format in here as well. So this works well because I already have my ranges named. So here's my amount range I can see all of my numbers under.
The amount column are selected. My expenses and then my office as well. Now we can repeat the same thing in order for us to calculate the average cost per meal. It's going to be the same thing. The expense range. Our criteria is meal and we want to run the numbers on the amount column. So I'll go ahead. And this time I'll insert the average if function. This will give me the average.
Per meal expense. So I'll search for average if I'll press go and I'll press okay. So same thing range expense meal. So range is going to be expenses again. My criteria are the meals. So I'll click here. And the range that I want to run the numbers on. While the only range is the amount range. So I'll type amount..
And there you go. So the average meal expense is $590.14. So I'll go ahead and press okay. Again I'll give this the. Currency format. I'll just use the format painter this time. And there we go. So three down one to go. But as we can see it's always asking us for the same thing. What is the range. What is the criteria and what is the column of interest that you want to run your numbers on? The function takes care of everything for us in the background,.
A lot of calculations that we cannot see, that we're not privy to. All we're asked to do is just point to the different range. And what is the criteria as well. Now, what I can do, because we're using the cell reference. What is the average expense for let's say office supplies. So I can type office supplies in here. And it gives me the answer right away. So 650 and 33. Go ahead and write your sum. Have an average if..
