Nested If statements

[mikekoda]

Can someone help me please.

I want to know how I can make an if then statement for employee pay. I have seven different levels of pay. B, C, D, etc.
Under each level of pay are different rates. If level B compensated employees make less than 75 units then they should be paid $8 per unit. But if they make between 75 and 90 units they should be paid at a rate of $8.50 and so on.

Please assist!

UNITS	<75	75	90	104	115
B	$ 8.00	$ 8.50	$ 8.75	$ 9.00	$ 9.50
C	$ 8.50	$ 9.00	$ 9.25	$ 9.50	$ 10.00
D	$ 8.75	$ 9.25	$ 9.50	$ 9.75	$ 10.50
E	$ 9.00	$ 9.50	$ 9.75	$ 10.00	$ 11.00
F	$ 9.25	$ 9.75	$ 10.00	$ 10.25	$ 11.25
G	$ 9.50	$ 10.00	$ 10.25	$ 10.50	$ 11.50
H	$ 9.75	$ 10.25	$ 10.50	$ 10.75	$ 11.75

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[S.H.A.D.O.]

Good afternoon,

I would setup a LOOKUP table and then use a formula to lookup that table and return the correct hourly rate.

I hope this hels.

 

[mikekoda]

How do I setup a Lookup table?

 

[MarcelBeug]

Why "nested IF-statements"? I think you just want to lookup a value in a matrix, based on 2 criteria: level and quantity.
First replace "<75" by 0, so all column headers are numeric.

Assuming data in A1:F8, try something like:

=INDEX($B$2:$F$8,MATCH(level,$A$2:$A$8,0),match(quantity,$B$1:$F$1))

replacing "level" and "quantity" with their respective cell references.

 

[Oeldere]

Book1
	A	B	C	D	E	F
1	UNITS	<75	75	90	104	115
2	B	$ 8.00	$ 8.50	$ 8.75	$ 9.00	$ 9.50
3	C	$ 8.50	$ 9.00	$ 9.25	$ 9.50	$ 10.00
4	D	$ 8.75	$ 9.25	$ 9.50	$ 9.75	$ 10.50
5	E	$ 9.00	$ 9.50	$ 9.75	$ 10.00	$ 11.00
6	F	$ 9.25	$ 9.75	$ 10.00	$ 10.25	$ 11.25
7	G	$ 9.50	$ 10.00	$ 10.25	$ 10.50	$ 11.50
8	H	$ 9.75	$ 10.25	$ 10.50	$ 10.75	$ 11.75
9
10
11	D	92	$ 9.50
Blad4

Cell Formulas
Range	Formula
C11	=INDEX($A$1:$F$8,MATCH(A11,$A$1:$A$8,0),MATCH(B11,$A$1:$F$1,1))

 

[mikekoda]

This works except for less than 75 column!

 

[Oeldere]

Book1
	A	B	C	D	E	F
1	UNITS	0	75	90	104	115
2	B	$ 8.00	$ 8.50	$ 8.75	$ 9.00	$ 9.50
3	C	$ 8.50	$ 9.00	$ 9.25	$ 9.50	$ 10.00
4	D	$ 8.75	$ 9.25	$ 9.50	$ 9.75	$ 10.50
5	E	$ 9.00	$ 9.50	$ 9.75	$ 10.00	$ 11.00
6	F	$ 9.25	$ 9.75	$ 10.00	$ 10.25	$ 11.25
7	G	$ 9.50	$ 10.00	$ 10.25	$ 10.50	$ 11.50
8	H	$ 9.75	$ 10.25	$ 10.50	$ 10.75	$ 11.75
9
10
11	D	40	$ 8.75
Blad4

Cell Formulas
Range	Formula
C11	=INDEX($A$1:$F$8,MATCH(A11,$A$1:$A$8,0),MATCH(B11,$A$1:$F$1,1))

 

[MarcelBeug]

This works except for less than 75 column!

… First replace "<75" by 0, so all column headers are numeric…