Creating a Grade Book in Excel

Recently my mother suffered a stroke, resulting in my life taking on dramatic changes in order to allow me to care for her. But, I know Dian has had similar challenges in her own life this year. Therefore, I wanted to help her get some content into TechTrax. Consequently, this article is shorter than those I've previously written for TechTrax. Hopefully, I'll be able to write longer ones again someday soon.

A few months back my first traditional bound book was published by John Wiley & Sons, Inc., Microsoft Office Specialist: Excel 2003 Study Guide. You can learn more about it at my website, http://personal-computer-tutor.com/mos_excelstudyguide.htm.

Although written for those studying for their MOS exam, it's really suitable for anyone wanting to learn more about Excel. One of the components that Wiley asked me to include, which I really love, is something called "Real World Scenarios." This is where I get to tell people how Excel can help them in real-life situations. Since I'm a teacher, many of the scenarios I included revolved around how I use Excel in my job. With that in mind, I wanted to share this tip and file with you.

I'm often asked how to get Excel's VLOOKUP function to pick numbers from a range and return a corresponding value for any number in that range (e.g., grades 90 to 100 are equivalent to a grade of "A"). So, that's what I demonstrate in this Real World Scenario in my book and I've included the actual Excel file for you to download. To understand this best, I'd suggest you read my first VLOOKUP tutorial that I wrote for a previous issue of TechTrax ( http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=446 ) so you understand how the function works. Then, use this tip (reprinted from my book, with permission from Wiley publishing) to adapt the formula so it doesn't require an exact match to find a result.

When to Use a TRUE Argument as a Range Lookup

In most cases, you will always use FALSE as the last argument in a VLOOKUP formula. However, sometimes you really don’t want an exact match, but would prefer a close match. Using TRUE as your final argument requires that your data is sorted in ascending order, and Excel will go through your table looking for a match. If it gets past the point where the match should have shown up if sorted in ascending order, it will back up and grab the previous one. Confusing? Well, here’s a story which makes sense of this.

I’m a teacher and I have a lot of worksheets which calculate grades for my students and return numerical values. However, I need the letter grade that corresponds to that number. So, I have a lookup table that lists the grade numbers in Column A, sorted in ascending order, and the corresponding letters in Column B. However, I don’t need a separate row for each number. I only need a row for the lowest number which corresponds to the letter. So, in my lookup table, I have 0 in A1 and F in B1; 60 in A2 and D in B2; 70 in A3 and C in B3; 80 in A4 and B in B4; and 90 in A5, and A in B5. Since my school bases it’s grades on A being equal to 90 or above; B equal to 80 to 89; C equal to 70 to 79; D equal to 60 to 69; and F equal to 59 or less.


Home \| Archives \| Authors \| About Us \| Subscribe/Membership \| Consultants \| Mouse Droppings \| Feedback \| Member Login