Assignment on Excel
Q1 Create the following Students Measurement List database in a new work sheet and perform the given functions and save the Work book as Students List A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Name Mathew Anjali John Maria Stephen Julie Total Maximum Average Minimum B Height 142.5 138 146 138.7 147.5 132 C Weight(kg) 46 49 30.5 43 44 56 D Foot length(cm) 20.5 20.4 22 20.3 20 20 E
Q2 Create an employee data base in Excel worksheet .Create a maximum of 25 records. The sample is given with the required fields. Name Dept. Grade Basic HRA DA PF Gross Pay Net Pay
I) Calculate HRA, DA , PF as follows : HRA = 30 % of Basic DA = 50% of Basic Pf = 10% of Basic
II) Perform the following calculations on the employee data base : Calculate Gross Pay for each employee using functions . Calculate the Total salary to be disbursed every month Calculate the Net pay for each employee usi8ng formulae ( Gross pay-PF)
Q3 Create an excel spread sheet , which contain trecords of four matches in the following format . And save tese sheet as [Link] file name Player name , Match 1 , Match 2 , Match 3 , Match 4, Total , Average , Highest run , Lowest run , Performance Apply following action to the above given sheet i) Calculate Total and average using Excel Function ii) Write comment on Sachin and Dravid cell (Comments 100s :30 , 50s :23) iii) Create a chart (Player V/S Run ) of above given data Q4 Create the following mark sheet as given conditions & formatting. Roll No. 1 2 3 4 5 6 Name Peter Neeraj Raj Girish Ram Ajay Avg. Marks of Subjects Window XP 23 28 34 32 25 34 ? Word 15 19 46 36 87 45 ? Total ? ? ? ? ? ? Max ? ? ? ? ? ? Min ? ? ? ? ? ? Per ? ? ? ? ? ? Result ? ? ? ? ? ? Grade ? ? ? ? ? ?
Conditions: i) Result should be determine under the following Rules: a) If mark of subject>20 then result=Pass with blue colour Otherwise Fail with red colour b) If percentage between 21 & 25 then grade=D c) If percentage between 26 & 30 then grade=C d) If percentage between 31 & 35 then grade=B e) If percentage between 36 & 50 then grade=A f) To display all <20 marks in Red colour with double underline style g) To display Per(%) with two decimal places. h) Display Min & Max value using function.
i) To apply attractive formatting to work sheet j) Display Min & Max value using function k) To apply attractive formatting to work sheet Q5 Find out Factorial and Square root of following number: i) 100 ii) 25 iii) -4
