KEMBAR78
Excel Tasks for Data Analysis | PDF | Spreadsheet | Microsoft Excel
Scribd
Upload
103 views14 pages

Excel Tasks for Data Analysis

1) The document provides instructions for multiple Excel assignments involving entering data, performing calculations, formatting cells and charts, and generating reports. 2) Assignment details include tasks like entering rainfall data by city and month, calculating totals, adding formulas, and creating a column chart. 3) Another assignment involves entering employee payroll data by name with columns for basic pay, deductions, allowances, tax, PF, and generating payroll statements. 4) Formulas are to be added to calculate totals, percentages, and grades based on criteria for a student marks sheet with names and subjects.

Uploaded by

rhythm mehta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
103 views14 pages

Excel Tasks for Data Analysis

1) The document provides instructions for multiple Excel assignments involving entering data, performing calculations, formatting cells and charts, and generating reports. 2) Assignment details include tasks like entering rainfall data by city and month, calculating totals, adding formulas, and creating a column chart. 3) Another assignment involves entering employee payroll data by name with columns for basic pay, deductions, allowances, tax, PF, and generating payroll statements. 4) Formulas are to be added to calculate totals, percentages, and grades based on criteria for a student marks sheet with names and subjects.

Uploaded by

rhythm mehta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 14

MS.

EXCEL
Assignment 9
Q. Type the following data in excel worksheet, the values of X in
column A and values of Y in column B.

X 370 6166 684 449 643 1551 616 403


Y 70.5 53.5 65 76.5 70 71 60.5 51.5

Do the following:
Ans. (a) Complete column C for finding product x * y

(b) Find sum of X column at the end of data


(c) Find sum of Y column at the end of data

(d) Find sum of X * Y column at the end of data


(e) Find sum of X^2

(f) Find sum of Y^2


(g) Find the minimum from the values of X and Y both

(h) Count the values of X which are less than 1000.


Assignment 10
Q. A university maintains a year wise result for four courses and then
generates an average report as given below

Sno. Year Course1 Course2 Course3 Course4 Avg


1 2002 356 300 300 400
2 2003 200 400 200 450
3 2004 256 500 400 600
4 2005 400 600 500 550
5 2006 456 450 550 450
6 Total

(a) Complete the report to calculate the course wise average in


row

(b) Provide formula to calculate year wise average in column G


(c ) Generate a column chart to compare data

Assignment 11
Q. Use assignment 10 to perform the following formatting operations
(a) Draw a border around the worksheet

(b) Change the font size of heading to 14 points and underline it


and hide column c

(b) Increase the width of column A to 15 characters

d) Right Align the values in column B, C, F

Assignment 12
Q. The table below outlines rainfall levels, in millimetres, for seven
cities during a six- month period:
RAINFALL(MM)

CITY JAN FEB MAR APR MAY JUN

BRININGHAM 56 51 47 42 30 22
BRISTOL 64 58 53 47 34 24
GLASGOW 76 68 65 56 42 26
LIVERPOOL 59 50 51 43 30 23
LONDON 49 52 42 33 26 17
SHEFFIELD 53 54 49 44 35 24
SOUTHAMPTON 42 45 36 29 20 11

Enter these details onto a spreadsheet and complete the required


tasks:
Ans. 1. Widen the first column to 15

1. Add rows beneath to show the monthly rainfall


2. Delete blank rows or columns

3. Add a new column after the June rainfall statistics to show the
total rainfall in each city over the period
4. Name the spreadsheet as RainFall

6. The rainfall in Birmingham during March should be 58

7. Insert a new row between the rows holding the London and Sheffield
rainfall statistics. Enter the following details: Newcastle 65 63 57 50
39 21

Assignment 13
Q. A company records the details of total sales (in Rs. ) sector wise and
month wise in the following format
Jan Feb March April
Sector 30 12000 17000 14000 15000
Sector 22 14000 18000 15000 16000
Sector 23 15000 19000 16000 17000
Sector 15 16000 12000 17000 18000
Ans. (a) Enter the data in a worksheet and save it

(b) Using appropriate formula, calculate total sale for each sector

(c) Create a 3-D column chart to show sector wise data for all four

Assignment 14
Q. Prepare a worksheet to produce a payroll statement for ABB Ltd.
Employing 10 persons, given the following information
Emplo A B C D E F G H I J
yees
Basic 6500 7500 8200 9800 10200 12200 14600 15200 16600 18900
DA=40% of basic
HRA=15% of basic + DA
Conveyance=12% of basic + DA
PF=12.5% of basic + DA
Tax=12.5%

Ans. a. DA

b. HRA

c. Conveyance

d.PF
e. Tax

Assignment 15
Q. Enter the following data and save it.
Name Marks1 Marks2 Marks3 Total Percentage Grade
Amit 80 70 80
Renu 70 60 90
Rajeev 60 50 80
Manish 50 30 90
Sanjeev 40 40 80
Anita 70 70 90
Do the following
(a) Compute the total marks and percentage of each student by entering
appropriate formula.
(b) Compute the grades based on following criteria
If percentage >= 90 then grade = A
If percentage >= 80 and <90 then grade = B
If percentage >= 70 and <80 then grade = C
If percentage >= 60 and <70 then grade = D
If percentage < 60 then grade = E

You might also like