Help with "IF" formula

SandyD

New Member
Joined
Sep 25, 2006
Messages
3
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

SandyD

New Member
Joined
Sep 25, 2006
Messages
3
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,424
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 6, 2005
Messages
188

ADVERTISEMENT

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
EFGHIJ
57NameYrsofservicePay
581200James2400
592400Patty4400
605700Eric111000
61101000Jimmy151200
62151200Sheila181200
Sheet1
 

SandyD

New Member
Joined
Sep 25, 2006
Messages
3
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,424
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 6, 2005
Messages
188
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.
 

Forum statistics

Threads
1,137,337
Messages
5,680,900
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top