Help with "IF" formula

[SandyD]

I am trying to sep up a spreadsheet that will caculate the pay for longevity. I have the emplyee names in A2, the years of service in B2. I want Excel to look at the number of years and enter the amount to be paid. My data is as follows:

1 yr = 200
2 yr = 400
5 yr = 700
10yr = 1000
15 yr = 1200
20 yr = 1400

I have tried every combination of "IF" statements with no luck. Can anyone help.

Thanks,

Sandy D.

 

[mhenk]

why not use a vlookup?

 

[SandyD]

I am using Excel 2003 and am not familiar with vlookup? I sthis something that you could offer some guiadiance with? I sure hope so. This would make this so much easier for payroll.

Sandy D.

 

[Joe4]

Take a look at Excel's help on VLOOKUP. They have a detailed explanation, though I think the examples make it more apparent on how to use it.

 

[xcellnoob]

Here you go.
Basically the table to the left has the years of service correlating with the amount of pay. The VLOOKUP formula always rounds down, so someone with 14 YOS would get the 10 year pay. Make sense? Hopefully.

Master test.xls
	E	F	G	H	I	J
57				Name	Yrsofservice	Pay
58	1	200		James	2	400
59	2	400		Patty	4	400
60	5	700		Eric	11	1000
61	10	1000		Jimmy	15	1200
62	15	1200		Sheila	18	1200
Sheet1

 

[SandyD]

Thanks for all of the help. I think this VLOOKUP will do what I need. I will need to make sure it doesn't round down, or I'll have some employees ready to run me out of town- LOL.

Sandy D.

 

[Joe4]

I will need to make sure it doesn't round down,

Take a look at the 4th argument in the VLOOKUP function in Excel help.

If you set it to FALSE or 0, it will only return exact matches.
If you set it to TRUE or 1, then "if an exact match is not found, the next largest value that is less than lookup_value is returned".

 

[xcellnoob]

I will need to make sure it doesn't round down,

Take a look at the 4th argument in the VLOOKUP function in Excel help.

If you set it to FALSE or 0, it will only return exact matches.
If you set it to TRUE or 1, then "if an exact match is not found, the next largest value that is less than lookup_value is returned".

Good call. My bad.