IF statement to long, an easier way?
 

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

IF statement to long, an easier way?
 

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

IF statement to long, an easier way?
 

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

IF statement to long, an easier way?
 

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