Lab › Formulas & Functions
Formulas & Functions: Excel Lab
Marksheets, grades, bills, salary and lookups: the classic practical exercises. Pick a lab. Watch how, then do it yourself.
Student Marksheet
Make a result sheet: total marks, percentage, and Pass or Fail (pass mark 40%).
Rank the Students
Give each student a rank: 1 for the highest marks.
Grades with Nested IF
Give a grade for each student: 90+ = A+, 75+ = A, 60+ = B, 40+ = C, below 40 = F.
Grades with VLOOKUP
Same grades, but read from a grade table with VLOOKUP. Change the table later and every grade updates.
Salesman Bonus (Slab)
Bonus depends on sales: 30,000+ gets 1,500, 50,000+ gets 3,000, 80,000+ gets 5,000. Find each person's bonus and total pay.
Salary Sheet: DA, HRA, PF
DA is 40%, HRA is 20% and PF is 12% of Basic. The rates sit in one table so they can change. Find the net salary.
Electricity Bill (Slab Rates)
First 100 units cost 3 each, the next 100 cost 5 each, and every unit above 200 costs 7. Add a fixed charge of 50.
Orders and Sales by Region
Count the orders and add the sales for each region.
Shop Bill with GST
Make a shop bill: amount for each item, GST at 18%, the item total and the grand total.
Loan EMI Table
For each loan, find the monthly EMI, the total you pay back and the total interest.
