Make your own free website on Tripod.com

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 3rd and 4th row. She rectified her mistake by deleting the 4th 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