In the above excel table, Name, years of service and basic pay of employees are entered. Rank is given to an employee on the basis of years of service as follows:
And Bonus is percentage of basic as above. Write steps to assign Rank in column D and Compute Bonus in column E using VLOOKUP().
Year of Services
Upto 5
Above 5 upto 15
Above 15 upto 25
Above 25
Rank
Silver
Gold
Platinum
Diamond
Bonus
10%
15%
25%
40%
To give rank in column D.
Enter the formula,
=VLOOKUP(B2,$G$4:$H$7,2)
in cell D2 and then press enter key, using the fill handle of cell D2, copy the formula to cells D3 :D20, by dragging the fill handle up to cell D20.
To compute Bonus in column E.
Enter the formula,
=VLOOKUP(D2,$H$4:$I$7,2)
in cell E2 and then press enter key, using the fill handle of cell E2, copy the formula to cells E3 :E20, by dragging the fill handle up to cell E20.
23) In a Worksheet Name and Percentage of marks are entered in cells A2:A1002 and B2:B1002,Explain the steps to fill cells C2:C1002 with letter grade using VLOOKUP().
The Policy is Grading is as follows:
Marks
<=40
>40 and <=60
>60 and <=80
>80
Grade
D
C
B
A
To compute letter grade using VLOOKUP()
Enter the policy of grading as follows:
Enter 0,41,61,81 in cells D2,D3,D4 and D5
Enter D,C,B,A in cells E2,E3,E4 and E5.
Enter the formula
=VLOOKUP(B2,$D$2:$E$5,2) in cell C2 and press enter key.
And then press enter key, using the fill handle of cell C2, copy the formula to cells C3:C1002 , by dragging the fill handle up to cell C1002.
The above worksheet shows details of account mobilization drive from 1st july to 5th july of Indian Bank in its five branches(NORTH,SOUTH,EAST,WEST,CENTRAL).This worksheet also shows A/C type(CA,SB,FDA),amount and Gender of new customers. Using above worksheet as source data write steps to create PIVOT tables to get the following information.
a)What is the daily total deposit for each branch.
To summarize data using pivot table
To find what is the daily total deposit for each branch.
Click on any cell on the table.
Click on insert Tab, then click on PIVOT table in table group,
create Pivot table dialog box appears.
Select new work sheet and click on OK .
A new worksheet to insert pivot table appears.
From the Pivot Table list drag date field to row labels
drag branch field to column labels
drag amount filed to å values.
The Pivot table is prepared.
b) How many account were opened at each branch, broken down by account type.
To find how many accounts were opened at each branch, broken down by account type.
Click on any cell on the table.
Click on insert Tab, then click on PIVOT table in table group,
create Pivot table dialog box appears.
Select new work sheet and click on OK .
A new worksheet to insert pivot table appears.
From the Pivot Table list drag branch field to row labels
drag A/C type field to column labels
drag amount filed to å values.
Right click on amount area in the table and select
‘summarize data by count’
The Pivot table is prepared.
c) To show frequency distribution of deposit amount as 1 - 5000,5001-10000,..for all new accounts.
To show frequency distribution of deposit amount as 1 - 5000,5001-10000,..for all new accounts.
Click on any cell on the table.
Click on insert Tab, then click on PIVOT table in table group,
Create Pivot table dialog box appears.
Select new work sheet and click on OK .
A new worksheet to insert pivot table appears.
From the Pivot Table list drag amount field to row labels
drag amount filed to å values.
Click on first column in pivot table area Right click and select grouping.
Enter 1 in starting at 5000 in By box and click on Ok
Right click on second colunm in the table and select
‘summarize data by count’
The Pivot table is prepared.
d)Gender(Male/Female) distribution of different type of accounts opened.
To summarize Gender(Male/Female) distribution of different type of accounts opened.
Click on any cell on the table.
Click on insert Tab, then click on PIVOT table in table group,
Create Pivot table dialog box appears.
Select new work sheet and click on OK .
A new worksheet to insert pivot table appears.
From the Pivot Table list drag Gender field to row labels
drag A/C typefield to column label
drag amount to å values.
Right click on second colunm in the table and select
‘summarize data by Sum’
The Pivot table is prepared.
e) Gender breakup of accounts opened in different Branches.
Gender breakup of accounts opened in different Branches.
Click on any cell on the table.
Click on insert Tab, then click on PIVOT table in table group,
Create Pivot table dialog box appears.
Select new work sheet and click on OK .
A new worksheet to insert pivot table appears.
From the Pivot Table list drag Branch field to row labels
Name and taxable income are entered in cells B5:B70 and C5:C70 respectively. Generate
income tax in cells D5:D70 rounded to nearest integer using the following policy.
Income
Income Tax
First Rs.100000
Nil
Next 250000
10%
Next 300000
20%
Excess
30%
To compute income tax in column D.
Enter the formula,
=ROUND(IF(C5<=100000,0,IF(C5<=350000,(C5- 100000 ) * 10 % , IF (C5<=650000,25000+(C5-350000)*20%,85000+(C5-650000)*30%))),0)
in cell D5 and then press enter key, using the fill handle of cell D5, copy the formula to cells D6 :D70, by dragging the fill handle up to cell D70.
Employee no. , Name and basic pay of 50 employees are entered in cells A3:A52,B3:B52 & C3:C52 respectively in a spreadsheet. Explain the procedure to find DA @26% of basic rounded to its nearest integer in cells D3:D52, HRA @ 30% of basic pay+da in cells E3:E52 ,PF deduction @ 10% of basic pay rounded to its nearest Rs. 10 in cells F3:F52 and gross pay in cells G3:G52. Also show column totals in cells C53:G53.
Show the value of Highest Gross in cell A55.
Show least value of gross in cell B55.
Show no. of rows where Basic pay is less than Rs. 20000 in cell C55.
To compute DA in column D,
Enter the formula,
=ROUND(C3*26%,0) in CELL D3. and then press enter key,
using the fill handle of cell D3, copy the formula to cells D4 :D52
By dragging the fill handle up to cell D52.
To compute HRA in column E,
Enter the formula,
=(C3+D3)*30% in cell E3 and then press enter key,
using the fill handle of cell E3, copy the formula to cells E4 :E52 by dragging the fill handle up to cell E52.
To compute PF in column F,
Enter the formula,
=ROUND(C3*10%,-1) in cell F3 and then press enter key,
using the fill handle of cell F3, copy the formula to cells F4 :F52 by dragging the fill handle up to cell F52.
To compute GROSS PAY in column G,
Enter the formula,
=C3+D3+E3-F3 in cell G3 and then press enter key,
using the fill handle of cell G3, copy the formula to cells G4 :G52 by dragging the fill handle up to cell G52.
To compute column total in cell C53:G53,
Enter the formula,
=SUM(C3:C52) in cell C53 and then press enter key,
using the fill handle of cell C53, copy the formula to cells D53 :G53 by dragging the fill handle up to cell G53.
To find highest gross
Enter the formula,
=MAX(G3:G52) in cell A55 AND PRESS ENTER KEY.
To find LEAST gross
Enter the formula,
=MIN(G3:G52) in cell B55 AND PRESS ENTER KEY.
To find NO. OF ROWS WHERE BASIC PAY IS LESS THAN 20000
Enter the formula,
=COUNTIF(C3:C52,”<20000”) in cell C55 AND PRESS ENTER KEY.
The following data has been entered in a Excel worksheet
Based on these values, write steps to
a) Arrange the data in the order of Dept and further in the order of Name.
Select Range of cells A4:C10 .
Click on data tab, in data tab click on click SORT ,sort dialog box appears then click on sort by select DEPT and click on COPY LEVEL and in THEN BY click on Name column and click OK button.
b) To display only those rows where salary is above Rs.15000.
Select entire table, click on DATA tab, in data tab click on FILTER in sort and filter group.
Now drop downs arrows appear on each column.
Click on drop down arrow of salary column.
Click on Number filters ,select ‘greater than’ option now custom auto filter dialog box appears and now enter 15000 And click on Ok.
c) To display all the rows except the ‘STORES’ dept.
Select entire table, click on DATA tab, in data tab click on FILTER in sort and filter group.
Now drop downs arrows appear on each column.
Click on drop down arrow of dept column and unselect ‘stores’.
In a Worksheet data about Name and Marks (out of hundred) in six subjects has been entered in columns A, B, C, D, E, F, G respectively in first 26 rows. In first row headings are entered, in remaining 25 rows data of 25 students has been entered. Explain the step to perform the following:
i) To find the total marks of each student in column H
Enter the formula
=SUM(B2:G2)in cell H2, and press enter key ,
using the fill handle of cell H2, copy the formula to cells H3 :H26,by dragging the fill handle up to cell H26 .
ii) To arrange the worksheet in descending order of total marks and further in the alphabetical order of names.
Select the full range of cells
Click on DATA tab in data tab click on SORT ,sort dialog box appears then click on sort by select total and click on Order and select largest to smallest,now cick on COPY LEVEL and in THEN BY click on Name column and select A-Z and click OK button.
iii) To display those rows where the total marks are above 450.
Select entire table, click on DATA tab, in data tab click on FILTER in sort and filter group.
Now drop downs arrows appear on each column.
Click on drop down arrow of total column.
Click on Number filters ,select ‘greater than’ option now custom auto filter dialog box appears and now enter 450. And click on Ok.
Year wise productions (In million tons) of Rice, Wheat, Jawar and Bajra have been entered in a Worksheet as shown below.
Based on these values, write steps
• To find annual production in column F
Enter the formula,
=SUM(B2:E2) in cell F5, and then press enter key,
using the fill handle of cell F5, copy the formula to cells F6 :F8 by dragging the fill handle up to cell F8.
•To draw column chart of production of Rice & Wheat for 4 years.
To draw column chart ,
Select range of cells B4:C8, now click on INSERT tab, in Insert tab click on column chart from CHART group, from sub chart select first sub chart.
Now select data source option from CHART TOOLS. Data source dialog box appears, to select year as horizontal axis labels,click on edit and select range of cells A5:A8 and click on Ok.
•To draw PIE chart for the production of Year 2002.
To draw 3D Pie chart for the year 2002, select range of cells B4:E4, keep the control key pressed and now select range of cells B6:E6 click on INSERT tab,in Insert tab click on PIE from CHART group, from sub chart select 3D Pie chart.
