• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with this statement - Excel

V

virusviki

New Member
If an employee works for three hours, he is to be paid 20% of basic salary. If employee works for 4 hours or more than 4 then 30% additional rate has to be paid of the assigned rate, and if employe works for 2 hours then 15% less of the assigned rate.


1) Please tell me how should I create formula of this statement.

2) what is the default assigned rate
 
Hi ,


The problem statement is not clear ; your statements are as follows :


1. If an employee works for 3 hours , he is to be paid 20 % of his basic salary.


This is straightforward ; =IF(Hours=3,Basic Salary*20%)


It does not specify what should happen if he / she works for less than or more than 3 hours.


2. If the employee works for 4 hours or more , then 30 % additional rate has to be paid of the assigned rate.


This is not clear ; why the words additional rate ? These were not used in the first statement ; why the words assigned rate ? ; Do these mean the same as Basic Salary ?


If the words additional rate don't mean anything , and if the words assigned rate are the same as Basic Salary , then we can add this to the first :


=IF(Hours=3,Basic Salary*20%, IF(Hours>=4, Basic Salary*30%)


3. If employee works for 2 hours then 15% less of the assigned rate.


Again , not so straightforward ; why the word less ?


If again , we ignore this word , then :


=IF(Hours=3,Basic Salary*20%, IF(Hours>=4, Basic Salary*30%, IF(Hours=2, Basic Salary*15%)


You need to define what should happen if the employee works for less than 2 hours.


If the words additional rate are relevant , then replace :


Basic Salary*30%


by


Basic Salary + Basic Salary*30%


If the word less is relevant , then replace :


Basic Salary*15%


by


Basic Salary - Basic Salary*15%


Narayan
 
Thanks for replying

The formula which my friend told me is quite confusing

If(Hour>=4,(Basic salary/240)*(130%*Hours completed)
 
Hi ,


I am as confused as you are by the formula you have posted.


Instead of bothering about formulae , can you please explain what you would like to do ?


1. The Basic Salary is for how many hours of work ? Is it for 8 hours of work , or 40 hours of work , or what ?


2. What is the standard hours of work ? Is it 8 hours a day , or 9 hours a day , or 12 hours a day or is it on a weekly basis , 40 hours a week , or what ?


130 % implies that it is 30 % more than the Basic Salary.


What does the 240 stand for ?


Only you can explain the significance of all these numbers.


Narayan
 
You must log in or register to reply here.
Back
Top