Computer Operator Practical Exam Ms-Excel Questions Collection.

I have collected and presented the questions asked from Ms-excel in the final stage of the Computer Experimental Examination for the posts of Computer Operator and Assistant Computer Operator under Public Service Commission Local Level, State Government, and Union. It is also useful in non-technical aspects. I will provide the solution later. If you have any queries, please comment in the comment box.

Q.No. 1. Perform the following calculation in MS-Excel.

Places	Target	Production	Achievement	Grade
Dolakha	4800	6000	125%
Sindhupalchwok	5000	4500	90%
Kavrepalanchwok	5000	5000	100%
Average	4933.33	5166.67	104.73%

a) Calculate grade A+ for more than 100% achievement; A for 100% achievement and B for below 100% achievement.

b) Create a bar chart to show the target and production of three places under observation.

Q.No.2 Enter the following data in your worksheet.

Loan Amount	13500
Loan Duration in Years	7
Rate
Interest

a) Enter formula for the rate so that it will be 14% if Loan Duration is less than 5 years and Loan Amount is more than one lacks, otherwise it should be 8%. (2)

b) Calculate Interest = Loan Amount x Loan Duration x Rate / 100 (1)

c) Change the value of Loan Duration as necessary to get 80000 in Interest. (2)

d) Apply a thick line border around the table and a single thin line for the inner lines. (1)

e) Enter “Interest Calculation” as the header and set the page number at the bottom center of the page. (2)

Q.No.3 Create a bar chart with the following data in Sheet 1. (5)

Chart Title: “Crop Production on 2005-2010”.

X-Axis: Year

Y-Axis: Production

	2005	2006	2007	2008	2009	2010
Rice	3476	3485	3488	3490	3499	3500
Wheat	3000	3200	3500	3700	3900	4300
Maize	2854	2967	3087	3154	3223	3410
Barley	4000	3800	4200	4500	4800	4000

Q.No.4. Calculate commission for following data in Sheet 1. (5)

SNO	Product	Cost Price	Selling Price	Commission Percentage	Commission Amount
1	Makita Hand Drill 3420	$3000.00	$4200.00
2	Philips Steam Iron PS243	$300.00	$385.00
3	Soni Camcord $C331	$1200.00	$1800.00

a. Commission is 7% for all (Selling Price-Cost Price) is more than $500, otherwise, it is 4%.

b. Commission Amount is calculated as Commission Percentage of Cost Price.

Q.No.5. Enter the following data in your worksheet and calculate values for empty cells. (5)

क्रम ;;;;संख्या	नाम थर	;;संकेत नं‍‌	cf=a= @)^%–^^	cf=a= @)^^–^&	cf=a= @)^&–^*	s"n	cf};t
!	kbdk|;fb sf]O/fnf	!#&(@	(*=@@	((	(*=*(
@	sdn/fh bfxfn	#$!#	!))	((=@	!))
#	/fdafa' e6\6/fO{	@$!#%	(#=^	(&	((

a. On the worksheet, configure the settings so that the marks in the third, fourth, and fifth columns will be automatically displayed in red color when they are less than 99. (2)

b. Create a line chart to show the increasing or decreasing trend in the share market in Sheet 2.

Organization	First Week	Second Week	Third Week	Fourth Week
Bank of Kathmandu	235	255	312	200
Everest Bank	110	150	180	212
Sanima Bank	300	235	200	220

c. Create lines for each bank in different colors and show the index to indicate which color is for which bank.

Q.No.6. Create a suitable pie chart to convey the following message: (3)

There are 28 students in a class. 10 are freshmen, 8 sophomores, 4 juniors, and 1 senior, 5 people did not answer the question.

Create the following table and calculate ‘Final Grade’ as the average of four exams. Award ‘Letter Grade’ A, B,  C, or D based on Final Grade is above 90, 70, 60, or below respectively. Alphabetize the list. Plot a bar chart of Final Grade. (7)

Name	Exam 1	Exam 2	Exam 3	Final	Final Grade	Letter Grade
Washington, Adam	80	88	87	94
Stewart, Olive	99	92	96	100
Danforth, Elsie	85	99	42	95
Elton, Roger	56	76	74	70
Banks, Michael	45	35	56	60
Chau, Lisa	100	90	95	100
McDonald, Wendy	75	88	97	89
Renfrew, William	90	90	85	89

 Q.No.7. Enter following data in a sheet named 1990. (2)

Product	Production
Rice	5000
Wheat	4800
Maize	5200
Barley	4500

Enter following data in a sheet name 1991.

Product	Production
Rice	3000
Wheat	2800
Maize	4500
Barley	2000

Enter following data in a sheet named 1992.

Product	Production
Rice	4000
Wheat	3600
Maize	4200
Barley	2800

a. Consolidate the sheets 1990, 1991, and 1992 using the Sum function. (4)

b. Created a bar chart to display consolidated data and enter chart title: Crop Production of Three Years. (4)

 Q.No.8.  Calculate the following Salary Sheet.

ENO	ENAME	DESIG	BASIC	ALLOWANCE	DEDUCTIONS	NET PAY

a.  Configure DESIG column in such a way that is can’t store more than 4 characters. The probable values for DESIG are ADMN, MNGR, ACCT & PEON. (2)

b . Create a formula for BASIC so that it is: (2)

Rs. 7000 for PEON, Rs. 14000 for ACCT, Rs. 18000 for MNGR, Rs. 22000 for ADMN.

c. ALLOWANCE will be 15% of BASIC if he/she is not ADMIN or MNGR. (2)

d. DEDUCTIONS will be 1% of BASIC for PEON and 11% for MNGR and ACCT. For ADMN deduct 15% of BASIC. (2)

e. Create range names so that you can type = ALLOW – DEDU as a formula for the NET PAY column. (2)

Q.NO.9. Calculate the following score sheet.

Symbol No	Eng	Nep	Mat	Sci	Percent	Grade

a. Use data validation to allow symbol numbers to have exactly 7 characters. (2)

b. Apply conditional formatting to display marks in red color if it is smaller than 50. (2)

c. Calculate Percentage and Grade. The grade is awarded as below. (4)

    i. A+ if any of the marks obtained is more than 90.

   ii. A if percent is greater than 60.

  iii. B if the percent is greater than 50.

  iv. C if the percent is below 50 or any of the marks is below 60.

d. Apply Colorful auto-format for the table. (2)

Q.No.10. Enter the following data in sheet 1 and display subtotal of each item and grand total. (6)

Date	Item	Quantity
12-Jan-00	Pen	25
13-Jan-00	Ink	28
13-Jan-00	Pen	27
14-Jan-00	Ink	10
14-Jan-00	Pen	15
14-Jan-00	Rubber	12
15-Jan-00	Carbon	25
15-Jan-00	Ink	8
15-Jan-00	Pen	22
15-Jan-00	Rubber	12
16-Jan-00	Ink	28
16-Jan-00	Pen	16
19-Jan-00	Rubber	10

a. Create a pie chart for item Pen with sectors for each date. (4)

Q.No.11. Enter the following data in spreadsheet and perform as asked. (10)

SN	EMP Code	Sale AMT	Commission	Earning	Tax	Net Earning
1	1001	5000
2	1002	3800
3	1003	4000

a. Enter above data in sheet 1. (2)

b. Calculate Commission as 7% of Sales AMT. (1)

c. Calculate Earning as the sum of Sales AMT and Commission. (1)

d. Calculate Tax as 13% of Earning for all Sales AMT equal or more than 4000. (2)

e. Calculate Net earnings as the difference of Earning and Tax. (1)

f. Create a column chart showing Earning and Net Earning for each EMP Code. (3)

Q.No.12. Create a line chart from following data. (10)

Product	January	February	March	April	May	June
Platinum	7000
Gold	2002
Silver	3003

Fill up the blanks cells with the following information.

a. The quantity of Platinum and Silver was increased by 20% in February and March.

b. The quantity of Platinum is decreased by 5% in April and 4% increment in May and June.

c. The quantity of Gold was steadily increasing by 3% every month.

d. The quantity of Silver was decreased by 2% in April, May, and June.

Q.No.13. Calculate the Increment using Ms-Excel.

A trading company distributes incentive salary to the staff 25000/- because of the company boomed up.

SN	Designation	Before Increment
1	CEO	198000/-
2	Manager	150000/-
3	.........	........
4	.........	........
5	.........	........

Calculate the increment Salary

a. Calculate the Percentage as

(After Increment Salary - Before Increment Salary)/Before Increment Salary * 100

b. Show the percent in the pie chart view.

Q.No. 14. Enter the following data in Excel Worksheet. (10)

Product	Date	Qty	Rate	Disc%	Amount
USB Extension		150	186	7
Pen Drive		3000	1600	12
Memory Sticks		500	668	7

Fill up the blanks cells with the following information.

a. Display current system data in the Date column in YMD format such as 13 Oct 2012.

b. Format the Rate column with one decimal digit and currency such as Rs. 186.0

c. Calculate the Amount for each row.

 Q.No.15: Prepare the following bill and calculate the bill amount for the customers. Also, create a suitable chart.

Cust.No.	Name	Current Meter Reading	Previous Meter Reading	Consumed Unit	Bill Amount
A1001	RajKumar Sharma	1250	1130
A1002	Hari Shankar	1320	1305
A1003	Anita K.C.	768	599
A1003	Sarita Thapa	1380	1125

Condition: Minimum Charge up to 20 units is Rs.80, the charge for 21 to 250 units are Rs.7.30 per unit and the charge for more than 250 units is Rs.11.50 per unit. 

To download the PDF file, click the below link.

Download the pdf file: Ms-Excel Practical Questions 

To view the PDF File

If you have any queries, please comment in the comment box.