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!

[TABLE="width: 427"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]UNITS[/TD]
[TD]<75
[/TD]
[TD="align: right"]75
[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]115[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] $ 8.00[/TD]
[TD] $ 8.50[/TD]
[TD] $ 8.75[/TD]
[TD] $ 9.00[/TD]
[TD] $ 9.50[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] $ 8.50[/TD]
[TD] $ 9.00[/TD]
[TD] $ 9.25[/TD]
[TD] $ 9.50[/TD]
[TD] $ 10.00[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] $ 8.75[/TD]
[TD] $ 9.25[/TD]
[TD] $ 9.50[/TD]
[TD] $ 9.75[/TD]
[TD] $ 10.50[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD] $ 9.00[/TD]
[TD] $ 9.50[/TD]
[TD] $ 9.75[/TD]
[TD] $ 10.00[/TD]
[TD] $ 11.00[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD] $ 9.25[/TD]
[TD] $ 9.75[/TD]
[TD] $ 10.00[/TD]
[TD] $ 10.25[/TD]
[TD] $ 11.25[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD] $ 9.50[/TD]
[TD] $ 10.00[/TD]
[TD] $ 10.25[/TD]
[TD] $ 10.50[/TD]
[TD] $ 11.50[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD] $ 9.75[/TD]
[TD] $ 10.25[/TD]
[TD] $ 10.50[/TD]
[TD] $ 10.75[/TD]
[TD] $ 11.75
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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…