What it shows
Formulas work out each student’s total, percentage, grade and pass or fail, and nested IF handles the grade bands. It shows how report cards can be calculated automatically.
The science
A nested IF tests conditions one after another: if the first is false it tries the next. The tests must go from the highest band to the lowest. AVERAGE and SUM work on a row of marks, and MIN finds the lowest mark, which is useful because a student fails if any subject is below the pass mark. Absolute references like $B$1 do not change when a formula is copied.
Materials
Cost: free. Many items come from home or from waste; spare parts such as LEDs, motors and wire are in the store.
- A computer, laptop or phone with a browser (free)
- A spreadsheet program: LibreOffice Calc (free) or Google Sheets (free)
- Made-up marks of 10 students in five subjects out of 100
- A notebook and a pencil
Safety
This project uses safe, everyday materials. Still, read the rules below with a parent before you start.
Never use mains electricity: power models with cells, batteries or a low-voltage adapter. Sharp and hot tools, flames and lab chemicals need an adult or your teacher beside you. Keep magnets, button cells and small parts away from young children, and wash your hands after making.
All the safety rulesStep by step
8 steps. Do them in order, and test each part before you go on to the next. Write what you notice in a notebook as you go.
In row 1 type Name, Maths, Science, English, Hindi, Social, Total, Percent, Grade, Result in A1 to J1.
Enter 10 made-up students from row 2 with marks in columns B to F.
In G2 type =SUM(B2:F2) and in H2 type =G2/500*100, then format H as number with one decimal.
In I2 type =IF(H2>=90,"A1",IF(H2>=75,"A2",IF(H2>=60,"B",IF(H2>=45,"C","D")))) for the grade.
In J2 type =IF(MIN(B2:F2)>=33,"Pass","Fail") so that one subject below 33 gives Fail.
Select G2:J2 and drag the fill handle down to row 11 to copy all formulas.
Add a row at the bottom: in A13 type Class average and in B13 type =AVERAGE(B2:B11), then copy to F13.
Make a mark lower than 33 for one student and check that the result changes to Fail even when the percentage is high.
What you should see
Each student row gets a total, percentage, grade and pass or fail result automatically, and the class average appears for every subject.
Viva questions
A teacher or a judge will ask how it works. Try to answer each question before you open it, then compare.
1Why do the IF tests go from high to low?
The first true test wins, so a 95 would be caught by the first test and not fall into lower grades.
2What does MIN(B2:F2) give?
The lowest mark in the row.
3What does the fill handle do?
It copies a formula to the cells below with the cell addresses adjusted.
4What does $ do in a cell address?
It fixes the row or column when the formula is copied.
Take it further
Add a column with the subject in which each student scored the highest using INDEX and MATCH with MAX.
Change one thing, measure what happens, and add a table or chart of your readings: that is what turns a model into your own investigation.
Free ebooks for this project
Computer & office
MS Excel Study Book
The complete study notes, practice questions and glossary for MS Excel
Computer & office
Excel Formulas Workbook
Sixty formulas and functions, each with a worked example from school, shop or office