MS Excel - IF Function and Letter Grades
In this tutorial I will show you how to use one of the logical functions in Excel, the IF statement and to demonstrate the use of IF, I’ll use simple grade book. Let’s assume that we have this as a grade book. We have some students in grade book. We have something’s that going to be graded on. We’re going to have a total on average and then we also want Excel to bring back a letter grade based on the average. And maybe this is your grading scale 90 to a 100 is an A, 80 up to but not including 90 is a B and so forth.
So how could you get Excel to bring back on these grades based on your grading scale? That’s a good application of IF statement, one of the bullion functions in excel. So let’s see how we go back during that. First I'm going to add just some sample data here to test our formulas and make sure that they're working properly. So for Emily I'm just going to put 100 in each one of these items.
Most of you know that for total you can just use equals, sum and then select the items that you want Excel to sum up. So there are the—I missed one, it should all be through F2, E2 through F2. So E2 through F2 is 400, I can check it real quick here to see that that’s working the way that it should. So that’s good. For average, another really easy formula is just equals average and then select the items you want to averaged. So I’ll select the same ones and hit Enter and there are my averages. I can test that to make sure that the average formula is working the way that it should.
Once I’m happy that those are working, then I can go about putting in the IF statement in order to test either average or what total in order to return a letter grade or anything else for that matter. So let’s use average. Here’s my grading scale how would I go about putting a formula here to bring back one of this letter grades. Well it’s really simple with the IF statement. And there’s a lots of applications and this is just one of the simplest ways for me to sort of demonstrate it. So you start off by going equals IF and then if you select on Excel it will show you the things that start with if the functions or formulas that start with it. I'm just going to double click on IF and it shows me how this formula is formatted. So for example there's a logical test, there's a value of that test is true and then there's a value of that test is false.
So my logical test could be IF H2 because my average that I'm testing for is in cell H2. So IF H2 is greater than or equal to some value in this case, it would be 90 so that’s our logical test. So now it says if this logical test is true and then what value do you want to return in cell I2 on the value? What I want to return is A and then comma. So IF h2=>90, and A will be placed here and then this logical test will stop and nothing else will happen. But what if that happens to be false that’s what it’s asking for here. Well if it’s false then what I want to do is execute another IF statement. So I would go IF open bracket and I'm going to follow the exact same pattern. Logical test, IF true, IF false, so you see the pattern here. So in this case I'm going to go IF H2≥80, then put a B there, comma. If false and then another IF statement. So IF H2≥70 C there and all I'm doing here is just following along with the values of my grading scale comma. Well what if that’s false well then I want one more IF statement, I’ll say IF H2≥60, “D”, and then what if this is false, what if this is false I don’t need to do anymore IF statements because I'm at the end of my grading scale.
If this is false then just bring back an IF. And that’s the end of my logical string and then I need to put as many closing brackets as I have opening brackets, in this case is four. One, two, three, four and hit Enter and now my formula should be here in the cell and if I've entered everything properly it should work. And I could test it here again by going through and just changing values 92.5 is still on A and 84.5 is a B and so forth. So I could go through it and change all my values to hit all of this sort of cut points to make sure that it’s working the way that I expect it to work and then if that’s the case and then I can take that formula and paint it all down to all my other students.
So it’s a pretty simple application of one of the bullion functions in Excel that a lot of people don’t even realized exist, that there are loads of this and they can be very useful and doing all kinds of things in Excel. So hopefully this has given you some ideas at least about how to use the IF statement.
Transcription by:
Scribe4you Transcription Services