EXCEL PRACTICAL-1 - To calculate GRADES using HLOOKUP

Sep 30, 2026

Upload by Đào

Workbook practice exercises on Excel formulas and lookups across three main question sets. Students must compute grades from marks using HLOOKUP, compute bonuses for salesmen using VLOOKUP, and compute bill amounts by mapping units to rates with LOOKUP, then calculate totals via multiplication. A second practical set continues with AVERAGE, IF/AND logic for PASS/FAIL and grade assignment, commission calculation using nested IF, and income tax plus surcharge and total tax based on tiered thresholds.

LanguageEnglish

CategoryExam

FormatPDF

Pages12

Reading Time30 min

AccessFree to download

Q. 1 Grades from marks (HLOOKUP)

Entering marks/grades table

Using HLOOKUP and dragging formulas

Q. 2 Bonus from sales (VLOOKUP)

Creating sale-to-bonus lookup

Using VLOOKUP and dragging formulas

Q. 3 Bill amount from units (LOOKUP)

Building units-to-rate mapping

Using LOOKUP and calculating bill amount

EXCEL PRACTICAL-1 Q. 1 The following worksheet contains Roll.Nos. & Marks in 5 subject of a student. Calculate his grades as per the following : Marks 0-40 40-50 50-60 60 & above Grades 4 3 2 1 | | A | B | C | D | E | F | | --- | --- | --- | --- | --- | --- | --- | | 1 | Roll No. | ENG | HINDI | SCIENCE | MATHS | SO. SCI | | 2 | 110 | 45 | 56 | 67 | 78 | 60 | | 3 | GRADE | | | | | | | 4 | | | | | | | | 5 | | | | | | | | 6 | | | | | | | | 7 | | | | | | | | 8 | | | | | | | | 9 | | | | | | | | 10 | | | | | | | | 11 | | | | | | | Answer: Enter headings & data as shown in columns A to F To calculate GRADES using HLOOKUP 1. Enter Marks & Grades in rows 8 & 9 as follows | | A | B | C | D | E | | --- | --- | --- | --- | --- | --- | | 8 | MARKS | 0 | 40 | 50 | 60 | | 9 | GRADE | 4 | 3 | 2 | 1 | 2. At B3 enter =HLOOKUP(B2, $B$8:$E$9 , 2) 3. Grade is displayed for the first subject. Drag the formula up to F3 to get grades for all the subjects. Q. 2 The following worksheet contains Names & Sale for 10 salesmen. Calculate their bonus as per the following : Sale 0-30000 30000-40000 40000-50000 50000-60000 60000-70000 70000-80000 80000 & above Bonus 0 3000 4000 5000 6000 7000 8000 | | A | B | C | D | | | H | I | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | NAME | SALE | BONUS | | | | 0 | 0 | | 2 | Deep | 30000 | | | | | 30000 | 3000 | | 3 | Jayesh | 40000 | | | | | 40000 | 4000 | | 4 | Yash | 45000 | | | | | 50000 | 5000 | | 5 | Sara | 48000 | | | | | 60000 | 6000 | | 6 | Gita | 55000 | | | | | 70000 | 7000 | | 7 | Jinal | 32000 | | | | | 80000 | 8000 | | 8 | Kavita | 66000 | | | | | | | | 9 | Minal | 23000 | | | | | | | | 10 | Naresh | 43000 | | | | | | | | 11 | Rima | 37000 | | | | | | | Answer: Enter headings & data as shown in columns A to F To calculate BONUS using VLOOKUP 1. Enter Sale & Bonus in columns H & I as follows | H | I | | --- | --- | | 0 | 0 | | 30000 | 3000 | | 40000 | 4000 | | 50000 | 5000 | | 60000 | 6000 | | 70000 | 7000 | | 80000 | 8000 | 2. At C2 enter =VLOOKUP(B2, $H$1:$I$7 , 2) 3. BONUS is displayed for the first salesman. Drag the formula up to C11 to get bonus for all the salesmen. Q. 3 The following worksheet contains Customer No. , Number of units consumed for 10 customers. Calculate their bill amount as per the following : Number of units Rate < 200 Rs. 3 >=200, < 500 Rs. 6 >= 500 Rs. 10 | | A | B | C | D | | | H | I | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | Cust.
No. | No.of
Units | Rate | Bill
Amount | | | Units | Rate | | 2 | 1101 | 340 | | | | | 0 | 3 | | 3 | 1102 | 180 | | | | | 200 | 6 | | 4 | 1103 | 400 | | | | | 500 | 8 | | 5 | 1104 | 600 | | | | | | | | 6 | 1105 | 350 | | | | | | | | 7 | 1106 | 470 | | | | | | | | 8 | 1107 | 890 | | | | | | | | 9 | 1108 | 200 | | | | | | | | 10 | 1109 | 500 | | | | | | | | 11 | 1110 | 360 | | | | | | | Answer: Enter headings & data as shown in columns H to ITo calculate RATE using LOOKUP 1. Enter Units & Rate in columns H & I as follows | H | I | | --- | --- | | 0 | 3 | | 200 | 6 | | 500 | 8 | 2. At C2 enter =LOOKUP(B2, $H$2:$H$4 , $I$2:$I$4) 3. Rate is displayed for the first customer. Drag the formula up to C11 to get bonus for all the customers. 4. In D2 enter the formula = B2*C2 5. Bill amount is displayed for the first customer. Drag the formula up to D11 to get bonus for all the customers. **************** EXCEL PRACTICAL – 2 Q1 . A worksheet contains Roll Number , Marks in 2 subjects for 50 students in a class. Calculate Result and Grade using the following: A student is declared as PASS if he gets 40 or more in both the subjects , Otherwise FAIL. All FAILED students will be given Grade IV For PASSED students Grade will be obtained as foll

FAQs

How are grades calculated in Q.1?

Grades are calculated by mapping mark ranges to grade values using HLOOKUP. After entering the marks and grades table, use HLOOKUP in B3 and drag the formula across the subject columns to fill grades.

How is bonus computed for salesmen in Q.2?

Bonus is computed by using a sale-to-bonus mapping table. Enter the mapping in columns H and I, then use VLOOKUP in C2 with B2 as the lookup value and drag the formula down for all salesmen.

How do you calculate bill amount in Q.3?

Rate is determined from the number of units using LOOKUP with a units-to-rate table. Then the bill amount is calculated by multiplying units by the derived rate, entering the formula in D2 and dragging it down.

Sep 30, 2026

Upload by Đào