#### Ccc Programs

Program [1]

(a) Create a worksheet "employee" with the following information.

 Name Phone Floor Office Dept Design Salary Neha 5834 2 11 Admin PM 8000 Sucheta 2812 2 21 Admin CL 9000 Amrit 3477 1 17 Mkt CL 10000 Arunesh 6992 3 14 Mkt MM 8500 Ashwin 1538 3 12 Mkt SI 5000

(b) To find floor in which there is no person whose name starts with A.

(c) To display name and office for the persons in office 11 and 14.

(d) To extract name and phone for the persons on the floor between 2 and 4.

(e) To extract name and office for persons of Admin department.

(f) To extract name and phone for the persons having salary more than 8000/-.

Program [2]

(a) In Spreadsheet, Enter the following data

 Employee Name Basic Pay DA HRA Gross Pay Insurance P.F. Net Salary Anil Kumar 8500 Sunita Devi 12500 Roji Sharma 16500 Farida Khan 18500 Gunita Devi 24000 Total Amount Highest Employee Salary Lowest Employee Salary Total Insurance Collected

(b) Calculate DA as 80% of Basic Pay and HRA as 10% of basic pay + DA.

(c) Find out Net Salary (giving the deductions of Insurance 10% of Basic pay and P.F. 5% of Basic pay).

(d) Find out the Highest and lowest Salary of the Employee.

(e) Calculate the total Insurance collected from all the employee.

(f) Apply two decimal notations to all the numeric values.

Program [3]

1. . Create the worksheet shown above and save the file with name “Employees_Detail”.

EMPLOYEE'S DATABASE
NAME
DEPARTMENT
DESIGNATION
SALARY
JUHI
SBI
BM
100000
LKO
HARISH
LIC
80000
KANPUR
ANANYA
SBI
RM
200000
BAREILLY
GAURAV
LIC
MD
90000
LKO
KAJAL
FCI
RM
200000
KANPUR
MAYANK
LIC
MD
80000
MEERUT

Do the followings :

(a) Merge and center the Heading "EMPLOYEE'S DATABASE". Apply the cell style to Accent2.

b) Make the Column headings in BOLD format.

(c) Count the total no. of employees DEPARTMENT WISE.

(d) List the name of employees whose salary is greater than 120000

(e) Count the total no. of employees who have salary greater than 100000

(f) Sort the data DEPARTMENT wise in ascending order

(g) Generate a Column chart and Line chart to highlight employee name and their salary.

(h) Generate a Pie chart along the salary contribution by each DEPARTMENT

Program [4]

A university maintains a year wise result for three courses and then generates an average report as given below :

 SR NO YEAR COURSE1 COURSE2 COURSE3 AVERAGE 1 2014 300 650 560 2 2015 450 500 400 3 2016 490 400 250 4 2017 600 650 350 5 2018 500 550 450

(a) Create the worksheet shown above.

(b) Save the file with name "Courses".

(c) Use the AutoFill to put the SR NO. into cells.

(d) Set the column widths as follows : Column A : 8, Column B : 14, Columns C & D : 15, Columns E & F : 14.

(e) Complete the report to calculate the course wise average in row 6.

(f) Provide formula to calculate year wise average in column F.

Program [5]

1. A university maintains a year wise result for three courses and then generates an average report as given below :

 SR NO YEAR COURSE1 COURSE2 COURSE3 AVERAGE 1 2014 300 650 560 2 2015 450 500 400 3 2016 490 400 250 4 2017 600 650 350 5 2018 500 550 450 Course Wise Average

(a) Create the worksheet shown above.

(b) Save the file with name "Courses".

(c) Use the AutoFill to put the SR NO. into cells.

(d) Set the column widths as follows : Column A : 8, Column B : 14, Columns C & D : 15, Columns E & F : 14.

(e) Complete the report to calculate the course wise average in row 6.

(f) Provide formula to calculate year wise average in column F.

Program [6]

Create a table in MS-Excel as shown below:

 Roll No. Name Marks in English Marks in Maths Total Marks 1. Rahul 85 95 2. Ronit 65 50 3. Amit 72 80 4. Rupesh 40 60 5. Shivika 35 60 6. Garima 87 91

Do the following:

a) In the total marks column, entries should be calculated using formulas and it is the sum of marks in physics and marks in chemistry.

b) Insert a new row at the end of the table and also find grand total using formula.

c) Sort the table based on total marks.

d) All columns should be center aligned.

e) Heading should be in bold and underlined