Add Assigned Formula with airSlate SignNow

Add assigned formula

hello Bridgewater college education 370 students in this screencast we're going to learn how to get Excel to determine the letter grade for us so that it can decide if a student gets a B+ and they - whatever based on our grading scale now we're going to use the same classes that we set up before in fact I'm going to use the same screencast both for those who did the grades on a point basis and the ones who did their grading book on a percentage basis so in front of me now I have the class that is set up to be graded on a point basis and as you recall we calculated averages for each of our students so what I want to do is to add a new column over here and column in and I want to put final grade here so let's just put grade I like for this to all be centered so remember to click on the end and then click on the center button and now we're ready to assign a grade to the student now the trick for this is that you have to put your grading scale somewhere in Excel I've chosen to put mine down here at the bottom of the page in row 25 through 36 and here I have the grades that are possible F D minus D D plus and so forth all the way to a and I also have a grading scale here happens to be the bridgewater grading scale so the lowest d you can get is a 59.5% now you might know that in order for me to type that in i had to type it as 0 22 5 9 5 and then i had to use the formatting option to convert that to a percentage and likewise to get the grade of D here i type 0.625 now this means that integrate between a 59 and a half percent to 62 and a half percent is going to get a d-minus the the number that's to the right of each letter grade then represents the lowest possible score you could get and receive that grade so the lowest possible score you could get and receive an a-minus would be an eighty nine point five which I would round to a ninety which would be an a-minus likewise to get an A you they have a 92.5 or higher 92.5 around the 93 and a 93 would be an a so I have my grading scale set up down here I'm sort of hidden out of the way so now let's go back to the top of our grading book and I want to have Excel determine what's the letter grade for Betsy according to my scale it should be a C+ because as you can see down here the lowest C+ is a seventy six point five and she's got a 77 all right so the way we're going to do this is the type of formula code lookup equal lookup l okay you P lookup parenthesis and I have to tell it what first of all what is it I want lookup I want the average look that's what click on average then I type a comma and I tell it where do I want that looked up well I want it looked up over here in this column of possible grades so I'll highlight the whole column then I type a comma and I tell it where I want it to decide based on what it found in that column in other words I'm going to click on the F through a I'll use closed parenthesis and when I hit my Enter key sure enough its assigned Betsy a grade of C plus so it is correctly looked up her grade C + based on the fact that her average came in a little bit higher than the minimum grade for a c-plus 76.5 now we learned in the last screencast how to copy a formula and you would think I could just put my cursor here and drag down and it would correctly copy the formula but I want to warn you the grades may not be correct and we need to fix that here's why when I got Betsy's great correct and then drugged to copy it to the other students all it did was it said okay there's a bunch of cells down here that you want to use and each time I went down one student from Betsy the gust for example it went down one eye in a row so it began calculating Gus's grade here to here that's not what I want I wanted to always use these values when it calculates great so what we're going to do is we're going to go back to the formula for Betsy and I'm going to tell it that the column B row 25 through 36 I wanted to always use just column B nothing else just row 25 through 36 and the way to do that is to put a dollar sign before and after each values I've got dollar sign B dollar sign 25 dollar sign B dollar sign $36 Sun a dollar sign 25 dollar sign a dollar sign 36 now again it's a little confusing but I'm telling it that I always want you to use these cell values down here when you calculate every student's grade now when I drag her grade down in this case the grades didn't change but that's sort of by luck so here we have Excel assigning our final grades now what's interesting is that remember that you are the teacher you're in charge of the grades here so for example if I want to to look at a particular students average maybe it's Thomas's and Thomas says why did I get an 8 out of 10 in participation I was I participated well and you say okay I'll change it to a 10 you do and his grade doesn't change one iota so it doesn't make him any difference whether he got an eight or a ten on the other hand Georgia if we make her expert issah pation grade instead of a nine it's a ten notice that her grade does move to a c-plus let's try the same for the people who grade on a percentage basis so I'm going to go to that class now once again I've got my grades over here to the right my final grades and I have copied the grading scale down here beginning in row 25 this time though I didn't use percentages I use numbers like fifty nine point five is the lowest d-minus eighty six point five is the lowest B+ and so forth so I'll use I'll put the word grade here I'll Center just as we did before I'm going to use that at lookup formula again equal lookup parentheses I wanted to look up the final grade comma I wanted to look up the final grade in this column right here comma I want it to assume that whatever it finds it finds a corresponding element in this column here close parenthesis and now I have the formula set but just as in the previous example I need to go in and put those dollar signs in each of these values because I want it to always use that exact table nothing else when it calculates the final grade I'll now drag a Betsy's grade on down to the others and we have their letter grades once again we might do some what ifs a Henry got a D in this class but remember he had that missing homework what if you said to Henry Henry you've not turned in his homework if you do your homework tonight and turn it in late I'll give you a maximum of 80 points so he does it he turns it in the next day we give him a maximum of 80 points and his great changes so that's the kind of thing that as a teacher you can use to your benefit and to give students the opportunity to perform well in in your classroom rewarding them when they achieve the grade that matches your grading scale well I hope this has been useful for you you