Consider the following salary sheet of a company based on the conditions which are as follows and which you calculate in your ms excel sheet.Â
In the salary sheet, you have seen that Monthly Salary, Income Tax, Social Tax, Net pay field are already filled, but it is clear that this field value is calculated by you, I have just written for you.Â
EmpCode
|
Name
|
Address
|
Monthly Salary
|
Income Tax
|
Social Tax
|
Net Salary
|
A001
|
Hari Sharma
|
Banepa
|
22500
|
3375
|
225
|
18900
|
A002
|
Ramita Gurung
|
Kathmandu
|
27000
|
4050
|
270
|
22680
|
A003
|
Anjana Giri
|
Bhaktapur
|
30000
|
6000
|
300
|
23700
|
A004
|
Sarita Ghimire
|
Sanga
|
20000
|
0
|
200
|
19800
|
- If the employee salary less than or equal to 250000, no income tax will be charged.
- If the employee salary greater than 250000 and less than 350000, 15% income tax will be charged.
- If the employee salary greater than or equal to 350000, 20% income tax will be charged.
Q.2 Calculate the social tax which is 1% of monthly salary.
Q.3 Calculate net salary as follows:-Â Â Â Â Â Â Â Â Â Â Â Â Salary-(Income tax+Social Tax)
Solutions:
=IF(E5*12<=250000,0,IF(AND(E5*12>250000,E5*12<350000),E5*15%,IF(E5*12>=350000,E5*20%)))
The above formula type in F5 Cell as you see in the figure below.
Let's compare this question to the natural language so that we are able to write algorithm code then accurate formula.
First Condition
Second Condition
This question is asked frequently in competitive exams, entrance exams, bank exams, governmental exams, interview exams, public service commission exam or any other exam.Â
IÂ suggest you some trick to solve this type of questions especially in case of "IF conditions" before in another Ms-excel conditions questions.
The trick is for the formula "how to write formula for IF Conditions" Let's start again as when you compare the question condition with your natural language. Write a question in your Language and convert it into an algorithm like code for it to solve.Â
For e.g.
if you come after 10:00 a.m., you will be charged as 5% of your Salary.Â
Algorithm code as
Let's you come after 10:00 a.m., then
If(Come Time>10, Salary-Salary*5%)
Now write the actual formula for it in your excel cell.
If you use this for all the conditions, you sure write the right formula for this question as well as for other questions also. Practice questions by writing the first algorithm then convert it into the right formula. If you did this, you sure complete this question in your job exam.
Below is the detailed solution to the above question, how to solve step by step.
Write the formula for the
Income Tax: -=IF(E5*12<=250000,0,IF(AND(E5*12>250000,E5*12<350000),E5*15%,IF(E5*12>=350000,E5*20%)))
The above formula type in F5 Cell as you see in the figure below.
Let's compare this question to the natural language so that we are able to write algorithm code then accurate formula.
First Condition
If the employee salary less than or equal to 250000, no income tax will be charged.If Salary <=250000, income tax will be zero (0)
Second Condition
If the employee salary greater than 250000 and less than 350000, 15% income tax will be charged.
If Salary >250000 and <350000, income tax will be Salary*15%.
Third Condition
Third Condition
If the employee salary greater than or equal to 350000, 20% income tax will be charged.
If Salary >=350000, income tax will be Salary*20%.Ok, let's move on Social Tax which we calculate.
Social Tax:Â
To calculate this cell you type the "=E5*1%"Â formula in G5 Cell. It's simple to calculate by multiplying the Monthly salary by 1%.
Net Salary:Â
To calculate this cell you type the "=(E5-(F5+G5))" formula in G5 Cell. It's simple to calculate by Subtracting the Monthly from the Total Tax.
Social Tax:Â
To calculate this cell you type the "=E5*1%"Â formula in G5 Cell. It's simple to calculate by multiplying the Monthly salary by 1%.
Net Salary:Â
To calculate this cell you type the "=(E5-(F5+G5))" formula in G5 Cell. It's simple to calculate by Subtracting the Monthly from the Total Tax.
If you have any queries, related to this question, ask me in the comment section without any hesitation. I would like to say thank you if this post is helpful for you or if I solve your problem through this question. Â Â Â Â Â Â Â Â Â Â Â Â
No comments:
Post a Comment