Breaking News
You are here: Home / Notes / SEM-1 / PC Software-I / MS EXCEL Assignment

MS EXCEL Assignment

Q-1) Calculate salary statement based on the criteria

(1)   Enter Eno, Ename, Basic

(2)   Calculate DA based on following condition

(3)

If  Basic < 20000  then

DA is 20 % of basic

(ii) If  Basic >= 20000 and  basic <=40000  then

DA is 40 % of basic

(iii) else

DA is 50 % of basic

(4)   HRA is 25% of basic

(5)   MA(Medical Allowance ) is fixed for all employee that is RS.500

(6)   PF is 25% of basic

(7)   Net Salary=(basic+DA+HRA+MA)-PF

(8)   Calculate Maximum and minimum average for column basic and net salary

(9)   Count the no employee whose net salary is >=50000

(10) Change the page orientation to landscape

(11) Change the printing order over then down

(12)Header row should be freezed

(13) Protect this ms excel file from unauthorized user

 

Q-2) Create the Student Mark sheet with the following fields

Roll No, Name, Address (city), Sub1, Sub2, Sub3, Sub4, Sub5

Solve the following queries

(1) Enter At least 10 records

(2) Add and calculate two fields total & percentage

(3) Insert one column and calculate the grade on following Criteria

 

IF per Grade
>=35 and <=49 Pass
>=50 and <=59 Second
>=60 and <=69 First
>=70 Distinction
Else Fail

 

(4) Count the number of distinction, first ,second ,pass and fail students

(5) Count the number of students in the class.

(6) Calculate the total for the distinction and first class student

(7) Calculate the total of each subject

(8) Format those students who is getting less than 28 marks with red bold +underline

effect

(9) Keep the validation on each subject i.e. between 0 to 70

 

 

 

 

Q-3) Consider the following table.

Sales Order Product Name Unit Sold State Country Name Land Area Housing Unit Population
1/01/06 Widgets 50 FL North 800 52,000 200,000
1/01/06 Gaskets 75 CA South 750 150,000 100,000
1/01/06 Gear 150 FL South 500 200,000 50,000
2/01/07 Widgets 125 FL South 450 120,000 200,000
2/01/07 Gaskets 100 CO West 100 180,000 300,000
3/01/07 Gear 250 CA North 230 200,000 250,000
3/01/07 Widgets 150 FL South 600 170,000 400,000
3/01/07 Gaskets 125 CA West 500 190,000 300,000
3/01/07 Gear 325 FL South 800 200,000 200,000

 

  1. Display Sales order after 2-jan, Product name beginning with letter ‘G’ and Unit Sold in Excess 100.
  2. Display all details; Sales date on 01-jan or 03-jan and number of Unit Sold Less then 150.
  3. List those records Sales date of 02-jan, Unit sold less then 150 and product Name ending in letter ‘ets’.
  4. Display records for countries in State of Florida with words North or South in Country Name and land area are more than 500.
  5. Display those records for Countries in the state of California or Colorado with population between 200,000 and 300,000 and having unit of more than 100,000.
  6. Subtotal state and country wise on land Area, Population, Housing unit.

 

Q-4)Create database with following fields:

ITEM_CODE, ITEM NAME, PUR_DATE, QUANTITY, RATE

  • Apply appropriate format to the data.
  • Add atleast 10 records
  • Add more field: DISCOUNT, PRICE and NET_PRICE

WHERE PRICE = QUANTITY * RATE

Discount is 10% of price if price >= 1000 else 5% of price

Net_price = price – discount

  • Header row should be freezed.
  • Display current date and time at right top corner.
  • Create report (Item name, purchase date and net price) using the Pivot Table Wizard.
  • The report will be shown as below in the new worksheet.
  • Edit the records in the database file and update the report.

 

Item name

Purchase Date

A    B    C

Grand Total

Grand Total

 

 

Q-5)Create a sheet as follows and do the following:

 

Roll no

Course

MS Word

MS PowerPoint

Total

1

CSCS

70

80

2

SDCS

60

75

3

DDCS

72

55

4

CSCS

79

49

5

CSCS

52

69

6

SDCS

68

78

7

SDCS

55

49

8

SDCS

69

71

9

DDCS

71

76

10

DDCS

83

77

 

▪  Create a 3D column chart for the column of Name, MS Word, MS PowerPoint using proper formatting options:

Title of chart: Students Report

Title of X axis: Students Marks

Title of Y axis: Students Name

▪  Add new column of “MS Excel” after MS Word column and enter the marks for it.

▪  Update your chat for this change.

▪  Find the max, min, average & total marks using the function.

▪  Give the grade using the following criteria:

Total >= 150                               First

Total < 150 & >= 125                Second

Total < 125 & >= 100                 Third

Total < 100                                  fourth

Download Paper Solutions from Below Link

About admin

Scroll To Top