What it shows
RANK orders students by total and VLOOKUP fetches one student’s full record by roll number to fill a report card. It shows how lookup formulas turn a data table into a report.
The science
RANK gives the position of a value among a list, with 1 for the highest when the order is descending. VLOOKUP searches the first column of a table for a key and returns a value from another column of the same row, so exact match FALSE is needed for roll numbers. Together they let one input cell, the roll number, drive a whole report.
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 20 students with roll numbers in five subjects
- 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.
On Sheet1 type headings Roll, Name, Maths, Science, English, Hindi, Social, Total, Rank in A1 to I1.
Enter 20 made-up students, using roll numbers 1 to 20 in column A and marks in C to G.
In H2 type =SUM(C2:G2) and copy down; in I2 type =RANK(H2,$H$2:$H$21,0) and copy down.
Check ties: two students with equal totals get the same rank; note what rank follows.
Select the table and use Data, Sort by Rank ascending to see the rank list.
On Sheet2 type Enter roll in A1 and a roll number in B1.
In A3 type Name with B3 =VLOOKUP(B1,Sheet1.A2:I21,2,FALSE) (in Sheets write Sheet1!A2:I21), then do the same for Total using column 8 and Rank using column 9.
Change the roll number in B1 and check that the name, total and rank change to match Sheet1.
What you should see
Sheet1 gives each student a rank, and Sheet2 shows the name, total and rank of any roll number typed in B1.
Viva questions
A teacher or a judge will ask how it works. Try to answer each question before you open it, then compare.
1What does the last 0 in RANK mean?
Descending order, so the highest total is rank 1.
2What does VLOOKUP search?
The first column of the table for the key.
3Why FALSE in VLOOKUP?
It forces an exact match.
4What happens to ranks on a tie?
Both get the same rank and the next rank is skipped.
Take it further
Replace VLOOKUP with INDEX and MATCH and explain why it still works when the key is not in the first column.
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