Worksheet N°3- MS-EXCEL

Exercise 1 :
For each student, the module manager enters the name, the EMD1 mark, the EMD2 mark and the practical work
mark. If the average of the three marks is >= 10 the result will be “pass”, otherwise the result will be “fail”.
1. Calculate the average of the students.
2. Determine each student's result.
3. Apply conditional formatting on means if the mean is less than 10; red pattern.
4. For each mark, calculate the number of absences ( the number of students with "0").

AT B VS D E F
the grade of
1 name EMD1 score EMD2 score the TP mean Result
2 Ali 3 5 0
3 Ahmad 14 9 10
4 Faiza 0 12 8
5 karim 15 0 14
6 mohammed 16 14 0
Number of
7 absentees/EMD

Exercise 2:
For each book purchased, the store manager enters its code, title, quantity and price. A discount of 15% is made
if the quantity is > 20, it is 10% if 20 >=quantity >=10 and 5% if the quantity is < 10.
1. Calculate the discount for each product purchased.
2. Calculate the amount (discount included).
3. Calculate the total (the sum of all amounts).
4. Calculate the VAT (the 7 % of the total) and the total including VAT (the Total + the VAT).

AT B VS D E F
invoice : L051
1
code
2 d title quantity price discounts Rising
3 L001 know and use your computer 30 400.00
4 C004 program databases 7 115.00
5 C002 number system 15 115.00
6 L003 algorithmic champion 3 350.00
7
8 total
9 VAT 7%
10 Total Price

Exercise 3:
National School of Computer Science of Sidi Bel Abbes 1st Year CPI- Office automation and web
Consider the following table, which makes it possible to calculate the salary and bonuses of employees of a
public company, such as:
 The premium1 is calculated as follows:
o 2000 DA given to women and administrators,
o 500 DA for others.
 The premium2 is calculated as follows:
o 3000 DA given to people whose TITLE is "Worker" or having a "salary without
bonus" less than 30000 ,
o 200 DA for others.
Give the formulas to put in the following cells:
 G2 = (prime1)
 H2 = ( prime2)
 I2 = (salary with bonuses)
AT B VS D E F G H I
SALARY without Salary with
No. NAME SEX TITLE CATEGORY Premium1 Prime2
1 Bonus bonuses
2 1 AT M Administrator 32000 4
3 2 B M Administrator 27000 3
4 3 VS F Secretary 22500 2
5 4 D M seller 18000 1
6 5 E M seller 31500 4
7 6 F M Worker 31500 4
8 7 G F Administrator 40500 4
9 8 H F seller 22900 2
Exercise 4:
Consider the following Excel table of shipping costs:

1. Calculate the average cost of box type shipments (G37).

National School of Computer Science of Sidi Bel Abbes 1st Year CPI- Office automation and web