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

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

In article ,
OxonLad wrote:

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

On Wed, 10 Oct 2007 10:29:00 -0700, OxonLad
wrote:

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_Employ ees,{1,11,20}),X,Y,Z)))


--ron

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:F 1,1),,),1))

HTH
Kostis Vezerides

On Oct 10, 8:29 pm, OxonLad wrote:
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