Ask a question »

Excel - Prepare grades from marks

July 2015

Mathematical and logical operations can be done on the data on an Excel workbook with the help of formulas. One of the most common applications of Excel is to compare rows of data such as preparing grades from marks. This can be done quite simply by using the "IF" logical operator in an Excel worksheet on the data of, for example, a particular column in the spreadsheet. A comparison with the marks will yield the corresponding grades. Generic formulas for more complicated comparisons are freely available and can be downloaded from the internet. The formulas can be changed for cell references and columns to suit the requirements of the worksheet.

Excel - Prepare grades from marks



Issue


I am a teacher. I entered the marks of my students in a column in an Excel 2007 spreadsheet. I have to prepare grades from the marks.

Marks from:
80 to 100 is "A" grade.
60 to 79 is "B" grade.
50 to 59 is "C" grade and so on. How do I enter a formula to prepare grades?

Solution


In my country we don't work with grades, but I thought there are 6 grades (A-F).

I made them up like this:

80-100=A    
60-79=B    
50-59=C    
40-49=D    
30-39=E    
0-29=F


I put a couple of marks in column A and put the following formula in B1 which I dragged down:
=IF(A1<=29,"F",IF(A1<=39,"E",IF(A1<=49,"D",IF(A1<=59,"C",IF(A1<=79,"B","A"))))) 


Change the formula (cell reference and condition) to suit your needs.

Note


Thanks to Trowa for this tip on the forum.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-prepare-grades-from-marks.pdf

See also

In the same category

Published by aakai1056. - Latest update by Virginia Parsons
This document entitled « Excel - Prepare grades from marks » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.