CPT
CPT
Some Important questions related to MS excel related to Computer Proficiency Test.
|
Q.1 Monthly salary of some employees are working in any
company is given below:- |
||||||||||||||
|
1A. Find out the
House Rent Allowance (HRA), if HRA is 45% of basic salary |
||||||||||||||
|
1B. Find out the Travelling Allowance, if Travelling
allowance is 10% of basic salary |
||||||||||||||
|
1C. Find out the Medical Allowance, if Medical
allowance is 15% of basic salary |
||||||||||||||
|
1D. Find the total annual gross salary |
||||||||||||||
|
1E. Calculate the income tax if, Tax slabs : Upto 2.5 lakhs - Nil, above 2.5 lakh upto 5 lakh - 5% , above 5
lakh upto 10 lakh - 10% , More than 10 lakh - 20%) |
||||||||||||||
|
1F. Calculate the Net Salary |
||||||||||||||
|
Name of employee |
Basic salary (Monthly) |
House Rent Allowance (HRA) |
Travelling Allowance |
Medical allowance |
Total Annual Gross Salary |
Income Tax |
Net Salary |
|
||||||
|
Vivek |
29200 |
|
|
|
|
|
|
|
||||||
|
Varun |
51100 |
|
|
|
|
|
|
|
||||||
|
Rahul |
56900 |
|
|
|
|
|
|
|
||||||
|
Mahesh |
69000 |
|
|
|
|
|
|
|
||||||
|
Ramesh |
50500 |
|
|
|
|
|
|
|
||||||
|
Suresh |
119300 |
|
|
|
|
|
|
|
||||||
|
Vatsal |
160300 |
|
|
|
|
|
|
|
||||||
|
Q2. Marks obtained of some students in a class in given
below, the maximum marks in each subjet is 25 |
||||||||||||||||||
|
2A. Find Total marks |
||||||||||||||||||
|
2B. Find the percentage of marks |
||||||||||||||||||
|
2C. Find the Grade, if Percentage of marks less in 50%
then Fail, if greater than or equal to 90% then Grade A, if greater than or
equal to 80% then Grade B, if greater than or equal to 70% then Grade C, if
greater than or equal to 60% then D, If greater than or equal to 50% then E |
||||||||||||||||||
|
Roll No |
First Name |
Hindi |
English |
Math |
Physics |
Chemistry |
Sum/Total |
% |
Grade |
|
||||||||
|
1 |
RAM |
24 |
23 |
22 |
24 |
20 |
|
|
|
|
||||||||
|
2 |
ASHOK |
24 |
12 |
14 |
12 |
18 |
|
|
|
|
||||||||
|
3 |
MANOJ |
25 |
24 |
25 |
14 |
17 |
|
|
|
|
||||||||
|
4 |
RAJESH |
15 |
14 |
8 |
16 |
20 |
|
|
|
|
||||||||
|
5 |
RANJANA |
14 |
17 |
10 |
13 |
18 |
|
|
|
|
||||||||
|
6 |
POOJA |
16 |
8 |
20 |
17 |
15 |
|
|
|
|
||||||||
|
7 |
MAHESH |
8 |
12 |
3 |
8 |
9 |
|
|
|
|
||||||||
|
8 |
ASHU |
12 |
16 |
7 |
14 |
12 |
|
|
|
|
||||||||
|
9 |
ANIL |
22 |
13 |
8 |
12 |
19 |
|
|
|
|
||||||||
|
10 |
PREM |
24 |
12 |
13 |
11 |
12 |
|
|
|
|
||||||||
|
3. Find the Student name and marks in English of Roll
no 1,5,8,3,6,10,9,2 using excel function not by copy paste and also named function/formula used. (Reference question no 2) |
||||||
|
Roll No |
Student Name |
English |
Physics |
|
||
|
1 |
|
|
|
|
||
|
5 |
|
|
|
|
||
|
8 |
|
|
|
|
||
|
3 |
|
|
|
|
||
|
6 |
|
|
|
|
||
|
10 |
|
|
|
|
||
|
9 |
|
|
|
|
||
|
2 |
|
|
|
|
||
|
4. Find the total marks, average marks, maximum mark,
minimum mark, Integer of average marks, combined first name and last name
also remove unwanted space |
||||||||||||||
|
Roll |
First |
Last |
Hindi |
English |
Math |
Physics |
Chemistry |
Sum/Total |
Average mark |
Maximum mark |
Minimum mark |
Integer of Average Mark |
First + last name |
Remove space |
|
1 |
RAM |
Singh |
24 |
23 |
22 |
24 |
20 |
|
|
|
|
|
|
|
|
2 |
ASHOK |
Verma |
24 |
12 |
14 |
12 |
18 |
|
|
|
|
|
|
|
|
3 |
MANOJ |
Gupta |
25 |
24 |
25 |
14 |
17 |
|
|
|
|
|
|
|
|
4 |
RAJESH |
Sharma |
15 |
14 |
8 |
16 |
20 |
|
|
|
|
|
|
|
|
5 |
RANJANA |
Rai |
14 |
17 |
10 |
13 |
18 |
|
|
|
|
|
|
|
|
6 |
POOJA |
Gautam |
16 |
8 |
20 |
17 |
15 |
|
|
|
|
|
|
|
|
7 |
MAHESH |
Kumar |
8 |
12 |
3 |
8 |
9 |
|
|
|
|
|
|
|
|
8 |
ASHU |
Jain |
12 |
16 |
7 |
14 |
12 |
|
|
|
|
|
|
|
|
9 |
ANIL |
Pandey |
22 |
13 |
8 |
12 |
19 |
|
|
|
|
|
|
|
|
10 |
PREM |
Roy |
24 |
12 |
13 |
11 |
12 |
|
|
|
|
|
|
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Sr |
Dept |
Name |
Gender |
Salary |
|
1 |
Admin |
Anita |
F |
15000 |
|
2 |
Admin |
Ram |
M |
14000 |
|
3 |
Finance |
Pooja |
F |
13000 |
|
4 |
Finance |
Mahesh |
M |
11000 |
|
5 |
Academic |
Priti |
F |
18000 |
|
6 |
Academic |
Suresh |
M |
17000 |
|
7 |
Finance |
Renu |
F |
15500 |
|
8 |
Exam |
Kamal |
M |
13300 |
|
9 |
Cheque |
Kiti |
F |
12500 |
|
10 |
Cheque |
Varun |
M |
11800 |
0