IF statement to long, an easier way?

[Guest]

I have an incentive matrix that pays a reward based on a range of
satisfaction scores. The target satisfaction score is also dependent on the
number of employees of the company being measured. As an example,

Payment X(<11) Y(>11<19) Z(>19)
500 60 63 68
1000 70 72 76
1500 80 82 84
2500 90 91 93

where x, y and Z are the number of employees and the payment is in the left
hand column. So, if a company with 15 employees achieves a score of 72.5
they get 1000, a score of 82.5 earns 1500 and so on

I have a cell containing number of employees (say A1) and the score (say B1)
with the payment header in, say, C1 and the rows/columns running from this.

At the moment I have a very long nested If statement that tests the numbers
of employees and then the survery score - there must be an easier way to
establish the intersection point and hence the payment but I can't see it.

Anybody help? I'd rather not VBA it

Thanks

 

[JE McGimpsey]

Don't have time at the moment, but the technique found here could
certainly be expanded to sample different ranges:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Ron Rosenfeld]

I have an incentive matrix that pays a reward based on a range of
satisfaction scores. The target satisfaction score is also dependent on the
number of employees of the company being measured. As an example,

Payment X(<11) Y(>11<19) Z(>19)
500 60 63 68
1000 70 72 76
1500 80 82 84
2500 90 91 93

where x, y and Z are the number of employees and the payment is in the left
hand column. So, if a company with 15 employees achieves a score of 72.5
they get 1000, a score of 82.5 earns 1500 and so on

I have a cell containing number of employees (say A1) and the score (say B1)
with the payment header in, say, C1 and the rows/columns running from this.

At the moment I have a very long nested If statement that tests the numbers
of employees and then the survery score - there must be an easier way to
establish the intersection point and hence the payment but I can't see it.

Anybody help? I'd rather not VBA it

Thanks

I named the ranges in the table: Payment X Y Z

Your ranges for numbers of employees are ambiguous. It is not clear what to do
with 11 employees, or with 19 employees. I assumed you did not want to omit
companies in these categories, so I grouped them as

X 1-10
Y 11-19
Z 20+

The following formula should return what you want, although may require some
slight tweaking depending on what you want to do at the "break points".

=INDEX(Payment,MATCH(Score,CHOOSE(MATCH(Num_Employees,{1,11,20}),X,Y,Z)))


--ron

 

[vezerid]

Create a table where you have 0 in place of X, 12 in place of Y and 20
in place of Z. All the other numbers stay as in your post. Say this
table is in C1:f5 (C1 contains the word Payment as per your post).

=INDEX(C2:C5,MATCH(B1,OFFSET(C2:C5,0,MATCH(A1,D1:F1,1),,),1))

HTH
Kostis Vezerides