Q11:
Remove blank Rows:
Step1:
Select the table.
Step2:
Click on Data Tab.
Step3: Click
on Filter (Symbol)
Step4:
Click on the Down Arrow next to Employee ID
Step5:
Deselect all except the Blanks
Step6:
Blank Rows will be displayed.
Step7:
Select them and delete them
Step8:
Click again on the down arrow of EmpID and select all.
Step9:
Blank rows are removed.
Q12: Highlight blank cells and fill
with “No Data”.
Step1:
Select the table.
Step2: Click
on find and select.
Step3:
Click on Go to special
Step4:Select
Blanks and Click OK
Step5:
Blank Cells are highlighted.
Step6:
Click on colour, Write No Data in one of the cells and then “Control + Enter”
Step7:
All the Blank Cells will be highlighted and are filled with No data.
Q13:
Concatenate the first name and the last name columns.
Step1:
Copy the columns First Name and Last Name in a new sheet
Step2:
In cell C1 write syntax
=
CONCATENATE(A1, “ – “ B1) and Click enter.
Step3:
Select C1 and drag down.
Step4:
The columns First name and last name are concatenated.
Q14:
Format the names of the concatenated column.
Step1:
In the column next to C write the syntax
= PROPER
( TRIM(C1)) and click enter.
Step2: Select
D1 and drag down.
Step3:
All the cells are formatted.
Q15: List
out all the employees whose salary is greater than $3,00,000 and less than
$4,00,000 give allowances of 5% and above $4,00,000 give allowances of
10%. Find the total allowances paid by
the company.
Note: A
little long syntax. This gives the
Nested if function loops, which helps us to understand how Excel eases our
calculations.
Step1:
Select the column Salary and paste in a new sheet.
Step2:
In Cell B2 write the Bonus
Step3:
In cell B3 write the syntax
=IF(AND(A3>0,
A3<=300000), A3*0, IF(AND(A3>300000, A3<400000),0.05*A3,
IF(A3>400000,0.1*A3, A3*0)))
And
enter.
Step4:
Select B3 and drag down.
Step5:
The bonus paid to all the employees will be displayed.
Step6:
If we want to calculate the total amount (salary with bonus) to the employees ,
write the syntax.
=IF(AND(A3>0,A3<=300000),((A3*0)+A3),IF(AND(A3>300000,A3<400000), ((0.05*A3)+A3),IF(A3>400000,((0.1*A3)+A3))))
And
click enter.
Step7:
Select C3 and drag down.
Step8:
The amount paid to employees will be displayed.
Select all and find the total which gives the total amount paid by the
company to the employees ( salary with bonus).
Comments
Post a Comment