Assignment

Ms-Excel:

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 Ms-Excel 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 Ms-Excel 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	27/3/98	1000	M
3	2	Ravina	34	Karate	20/1/98	1200	F
4	3	Karan	24	Squash	19/2/98	2000	M
5	4	Tarun	33	Basketball	1/1/98	1500	M
6	5	Zubin	36	Swimming	12/10/98	750	M
7	6	Ketaki	36	Swimming	24/2/98	800	F
8	7	Ankita	39	Squash	20/2/98	2200	F
9	8	Zareen	37	Karate	22/2/98	1100	F
10	9	Shaailya	37	Basketball	19/2/98	1700	M

6. What would be the steps to the quickest way of creating the following series in Ms-Excel?

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 India)
2	Payroll for employees (Temporary)
3	Today’s Date		26-Aug-09
4	Pay Rate		95
5
6	Worker’s Name	Hired on
7	Kushagra	3/3/09
8	Pradeep	3/3/09
9	Puneet	3/6/09
10	Rajiv	3/8309
11	Surbhi	3/2/09
12	Aditi	3/13/09

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 Ms-Excel.

	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 England and New Zealand on 13.02.2002.

Prepare a worksheet in Ms-Excel 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

England: M. Trescothick lbw b Butler 1, N. Knight c Adams b Tuffey 73, N. Hussain c Nevin b Adams 35, G. Thorpe run out 41, A. Flintoff c Vincent b Cairns 12, P. Collingwood c Tuffey b Vettori 9, C. White c Cairns b Vettori 0, J. Foster c Vincent b Vettoru 3, A. Giles c Harris b Cairns 2, A. Caddick c Harris b Adams 2, D. Gough not out 5. Extras (6lb, 6w, 1nb) 13 Total (in 40.2 overs) : 196 Bowling: Butler 5-0-37-1, Tuffey 8-0-48-1, Cairns 9-1-43-2, Adams 6.2-1-25-2, Astle 1-0-9-0, Vettori 8-1-17-3, Harris 3-0-11-0.

New Zealand: N. Astle not out 67, C. Nevin c Collingwood b Flintoff 55, S. Fleming c & b White 10, C. McMillan c Foster b Gough 15, C. Cairns b Gough 8, C. Vincent c Flintoff b Gough 0