Assignment
MsExcel:
1. Seema has been assigned to type the marks obtained in the first unit test by all the students in a class. She made 2 mistakes while typing the data. She entered Anil’s marks as 218 instead of 219 and she misspelled the name ‘PRITI’.

A 
B 
C 
1 
Name 
Roll No 
Marks 
2 
ANIL 
15 
218 
3 
PREETI 
25 
242 
What are the steps required to correct these mistake?
2. Monica typed the following data in a worksheet:

A 
B 
C 
D 
1 
Roll No 
Marks 
Name 

2 
101 
450 
MONICA 

3 
102 
519 
SAUMYA 

4 
103 
497 
SEEMA 

However, she types marks in column B instead of column D. What should she do to rectify the mistake?
3. Tania was typing out in an MsExcel worksheet the marks got by her classmates in the latest unit test. But she missed the information for one row after Monica's data and one column after roll no. what step did she follow to correct the omission.

A 
B 
C 
D 
1 
Roll No 
Name 
Marks 

2 
1058 
MONICA 
25 

3 
1059 
RUBY 
19 

4 
1060 
SAMY 
21 

4. Nidhi has created the worksheet shown below:

A 
B 
C 
1 
Name 
Roll No 
ADMN. No 
2 
EESHA 
2131 
1315 
3 
MOHIT 
2133 
1316 
4 
MOHIT 
2133 
1316 
5 
RUBY 
2134 
1317 
6 



But by mistake she typed the same information in the 3^{rd} and 4^{th} row. She rectified her mistake by deleting the 4^{th} row. How did she do it?
5. The sports teacher, Mr. Patil, asked Garima to enter the details of sports coaches hired by the school authorities in MsExcel and make the following changes:
a) Change the row height of 2 to 20 points.
b) Change the column width of column 8 to 15.
c) Delete the information stored in range A2 : B3 and shift the cells upward.
Could you help Garima with the task? The Worksheet is given below:

A 
B 
C 
D 
E 
F 
G 
1 
Coach_Id 
Name 
Age 
Sports 
Date of App. 
Pay 
Sex 
2 
1 
Kukreja 
35 
Karate 

1000 
M 
3 
2 
Ravina 
34 
Karate 

1200 
F 
4 
3 
Karan 
24 
Squash 

2000 
M 
5 
4 
Tarun 
33 
Basketball 

1500 
M 
6 
5 
Zubin 
36 
Swimming 

750 
M 
7 
6 
Ketaki 
36 
Swimming 

800 
F 
8 
7 
Ankita 
39 
Squash 

2200 
F 
9 
8 
Zareen 
37 
Karate 

1100 
F 
10 
9 
Shaailya 
37 
Basketball 

1700 
M 
6. What would be the steps to the quickest way of creating the following series in MsExcel?
i) 101, 102, 103, …….. 112
ii) MONDAY, TUESDAY,…………………… SUNDAY
iii) 105, 110, 115,…… 160
7. Given the following sales details for 4 salesmen. Calculate their commission using the commission rate 10% for the total sales.
Sales in
Salesman No. 
Qtr1 
Qtr2 
Qtr3 
Qtr4 
501 
3000 
3000 
4500 
5500 
502 
4000 
3500 
4200 
4300 
503 
4000 
4000 
4500 
4500 
504 
6000 
6000 
5000 
6000 
8. Given the following worksheet:

A 
B 
C 
D 
E 
1 
Rollno 
Name 
Term1% 
Term2% 
Final% 
2 
101 
Abhinav 
70 
75 
85 
3 
102 
Aashima 
60 
65 

4 
103 
Naeeka 
85 
90 
92 
5 
104 
Nabeera 
80 
90 
95 
6 
105 
Gurulingam 
70 
75 

7 
106 
Hemant 
72 
78 
84 
8 
107 
Rashmi 
68 
70 

9 
108 
Kushagra 
92 
95 
96 
10 
109 
Sukant 
55 
50 
57 
11 
110 
Neha 
40 
49 
65 
12 





13 
Top scorer’s percentage 




14 
Number of students appeared in final 



The weighted marks % is calculated as 25% if Term1 + 255 of Term2 + 50% of Final. Some students could not appear for final exams. Completer the above given worksheet using appropriate formulas.
9. Create the following worksheet and save the workbook as WAGES.XLS.

A 
B 
C 
D 
E 
1 
PACE COMPUTERS
(ATC CEDTI, Govt. Of 

2 
Payroll for employees (Temporary) 

3 
Today’s Date 




4 
Pay Rate 

95 


5 





6 
Worker’s Name 
Hired on 



7 
Kushagra 




8 
Pradeep 




9 
Puneet 




10 
Rajiv 
3/8309 



11 
Surbhi 




12 
Aditi 




i) Find out the number of days each worker has worked, by subtracting date on which worker was hired from today’s date.
ii) Calculate Gross wages for each worker. The gross wages can be calculated by using the following formula: Gross wages = no. of days worked * Pay Rate
10. Write commands for the operation (i) – (ii) based upon the spreadsheet shown below:

A 
B 
C 
D 
E 
F 
G 
H 
1 
Name 
Basic (Monthly) (Rs.) 
HRA (% of Basic) 
DA (Rs.) 
Total Salary (1997) 
Bonus (Rs.) 
Total (Salary) (1998) 
% Increase 
2 
Shirome 
5000 
10 
450 

1200 


3 
Somya 
6000 
15 
800 

200 


4 
Tanya 
7000 
12 
900 

1800 


5 
Aziz 
4000 
10 
500 

1700 


6 
Arjun 
8000 
12 
700 

1500 


7 
Kushagra 
9000 
12 
1000 

2000 


i) To calculate the total salary as sum of Basic Salary, HRA and DA for each employee for the year 1997.
ii) To calculate the total salary of each employee for the year 1998 as sum of salary for the year 1997 and bonus. Also calculate the percentage increase in the total salary from 1997 to 1998 for each employee.
11. Create the following worksheet in MsExcel.

A 
B 
C 
D 
E 
F 
1 
Roll No 
Name 
English 
Maths 
Science 
Total 
2 
101 
Ragini 
95 
78 
68 

3 
102 
Namita 
99 
97 
67 

4 
103 
Bharti 
89 
88 
87 

5 
104 
Aditi 
89 
98 
80 

6 
105 
Shirome 
80 
87 
88 

i) From the above worksheet find out the average marks for the entire class.
ii) Now copy the range consisting of the mentioned data (including total, average & class average) to a new location. Find out whether the cell references change or not.
iii) Now move the original data range to a new location. Find out whether the cell references change or not.
iv) Again move back the data to its original position and make changes in the marks obtained by students. Notice total, average and class average. What happens? Why does this happen?
12. PACE ICE CREAM PARLOUR maintains the details of items sold during the day in a workbook as shown in figure:

A 
B 
C 
D 
E 
1 
PACE ICE CREAM PARLOUR 

2 





3 
Flavour 
Quantity 
Rate 


4 





5 
Pista 
14 
7 


6 
Vanilla 
20 
12 


7 
Tuti Frooti 
5 
10 


8 
Cassata 
5 
35 


9 
Mango 
7 
15 


10 
Kesar 
4 
15 


Fig. P1.

A 
B 
C 
D 
E 
1 
PACE ICE CREAM PARLOUR 

2 





3 
Flavour 
Quantity 
Rate 
Amount 

4 





5 
Pista 
14 
7 
98 

6 
Vanilla 
20 
12 
240 

7 
Tuti Frooti 
5 
10 
50 

8 
Cassata 
5 
35 
175 

9 
Mango 
7 
15 
105 

10 
Kesar 
4 
15 
60 

11 
Total 


728 

Fig. P2.
Enter data in a worksheet as shown above and perform the following operations:
i) Save the workbook as SALES.XLS
ii) Minimize the workbook SALES.XLS and then restore it to its original size.
iii) Calculate the total sales (AMOUNT column in the worksheet given below) for each flavour by multiplying the QUANTITY and RATE.
iv) The management of ANTARCTIC CREAM PARLOUR wishes to find out the total sales at the end of the day. Modify the SALES.XLS book to fulfill the requirement. After calculating the total, the worksheet should look like the one shown in Fig. P2.
v) Change the rate of Kesar flavour from 12 to 15. Observe that the formulas get recalculated.
vi) Save the workbook again.
13. Create a worksheet as shown below:

A 
B 
C 
D 
E 
1 
Quarterly Sales Analysis 

2 
Salesman 
Qtr1 
Qtr2 
Qtr3 
Qtr4 
3 
A001 
73454 
17825 
47349 
87435 
4 
A002 
56347 
16243 
98466 
98464 
5 
A003 
23878 
87246 
46485 
62343 
6 
A004 
67235 
38265 
89458 
23654 
7 
Total 




i) Name the range B3 : B6 as ‘Qtr1’.
ii) Similarly define the name for the other ranges C3 : C6, D3 : d6 and E3: E6.
iii) Now find the sum of range Qtr1 in the cell B7.
iv) Also find the sum of the other ranges Qtr2, Qtr3, Qtr4 in cells C7, D7 and E7 respectively.
v) Create a chart depicting the sales in all the four quarters.
vi) Create an appropriate chart depicting the relation of salesman A001 in all the four quarters.
14. Prepare a doughnut chart based on the following data.
MONTH 
UNIT 
JANUARY 
3200 
FEBRUARY 
2200 
MARCH 
2600 
APRIL 
2800 
15. The
following is the summary of a one day international match played between
Prepare a worksheet in MsExcel so that the score board of each of the teams can be viewed in a tabular form. Apply formulae for doing totals of runs and other requirements.
Scoreboard