Multiple if Statement?

  • Thread starter Thread starter jeffmp21
  • Start date Start date
J

jeffmp21

I'm using Excel 2000 and have the following problem:
Column A has an employee's performance rating 10,20,30,40, or 50
Column B has the employee's current salary
Column C will calculate the employees increase 0, .5,
1.5,2.0,2.5,3.0,3.5,4.0,4.5,5.0
Column D has a min wage range
Column E has a middle wage range
Column F has the max wage
Columns B, D,E, and F are prepopulated.

Example employee has a 30 rating, makes $51,974, the min is 30,746, Mid=
43,043, Max = 55,097
Would receive a 2.5% raise
Based on:
Meeting expections (30 rating) but is in the upper third of the range
 
Very well explained but what is the question?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
You've left out some vital information.

What is the base raise?

How does performance rating affect the percentage given?

How does the position of current salary within the range affect the
percentage given?

Shouldn't column A be "prepopulated" too? If not, how should it be
calculated?
 
=IF(yousaywhatyouneed,maybewecanhelp,false)


--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"jeffmp21" escreveu:
 
JE McGimpsey said:
You've left out some vital information.

What is the base raise?
the rate is conditional based on employee rating (10,20,30,40,50) and where your current wage falls within in the range for the wage based on the jobcode.
Click to expand...
How does performance rating affect the percentage given?
Click to expand...
Rating and where your wage falls within the range for your jobcode determines the percentage given.
How does the position of current salary within the range affect the
percentage given?

Shouldn't column A be "prepopulated" too? If not, how should it be
calculated? You are correct A is also prepopulated.

The matrix looks like this
Click to expand...
Rating Under Range/ min Lower Third/Middle Third/Upper Third/Over Range
50 5% 4.5% 4% 3.5%
3.0%
40 4.5% 4.0% 3.5% 3.0%
2.0%
30 4.0% 3.5% 3.0% 2.5%
1.0%
20 2.0% 1.5% 1.0% 0%
0%
10 0% 0% 0% 0%
0%
in our example:
Click to expand...
A employee received a 30 rating and made $51,974.
the min for his class was $30,746, Mid = $43,045, Max = $55,097
He is therefore in the upper third and has a 30 rating so the raise is 2.5%
I need to calculate the raise for several thousand employees. All other
information is provided.
 
The matrix looks like this
Rating Under Range/ min Lower Third/Middle Third/Upper Third/Over Range
50 5% 4.5% 4% 3.5%
3.0%
40 4.5% 4.0% 3.5% 3.0%
2.0%
30 4.0% 3.5% 3.0% 2.5%
1.0%
20 2.0% 1.5% 1.0% 0%
0%
10 0% 0% 0% 0%
0%
in our example:
Click to expand...
A employee received a 30 rating and made $51,974.
the min for his class was $30,746, Mid = $43,045, Max = $55,097
He is therefore in the upper third and has a 30 rating so the raise is 2.5%

I need to calculate the raise for several thousand employees. All other
information is provided.

Sandy I hope that helps! thank you for helping!
 
As JE said you missed out vital information.

With the matrix that you supplied below in A2:F7 I created another table in
J2:O7 with the same headers "Rating", "Under Range" etc. with the rating
down Column J. All "Under Range" values were entered as zero and the other
values for the ranges entered as approptiate. The person's Rating was
entered in B14 and what the person made in C14. The formula:

=IF(COUNT(B14:C14)=2,HLOOKUP(INDEX(K2:O2,MATCH(C14,INDIRECT("K"&MATCH(B14,J3:J7,FALSE)+2&":O"&MATCH(B14,J3:J7,FALSE)+2),TRUE)),B2:F7,MATCH(B14,J3:J7,FALSE)+1,FALSE),"")

Then returned the value that you are looking for.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top