I helped @chris_1974 out recently adding look up tables into an excel document. I did promise then to do a How-To on it, and here it is.
I have put together a
google spreadsheet that goes through it in step-by-step, but here are the instructions!
Step 1 - add names.
Step 2 - Set up the sheet. In the example I have put in, there are two assessments and one homework. It is always much easier if you know what the sheet needs to look like before you start. The look-up tables at the bottom are the grades. At this stage we haven't done any formulae.
Step 3 - Add the data. Once you have your student's scores in the table, you can add percentages (not important for vlookup, just FYI). Note, I the formula I have put in =C2/$C$9 has two dollar signs. If you put the dollar signs around the letter of the cell that is going to stay constant, you can drag the formula down to the rest of the cells.
Step 4 - Once the percentages are in, start on the grades. In the same way as I added dollar signs earlier, they become crucial here. The formula for the first cell is: =VLOOKUP(C2,$C$12:$D$18,2). What does this mean? C2 - is Adam's score. C12:D18 is the data range we want to look up, and the 2 is the column you want to return in your cell. Once you have the first cell done, you can copy the formula down to the rest of the cells. Remember, when you are copying it to the 'homework' column, you will need to reset where you want the formula to calculate or it will give you duff results!
That is it!
If there are any other areas that I can help with, shout - more than happy to expand on the above!
