Enter a value in a cell depaending on the value of another cell

[Mr Lynton]

Hi guys, I am very new to excel and I am stuck. Basically I have a spreadsheet with all my staff on it and it calculates their start date to give me years employed. I know want another column to calculate their extra holiday entitlement. So after 5 years employed they get an extra 2 days holiday, and every year after 1 day. I need to know how to set a formula that will produce the right results e.g.

If column A1 is between 5 and 5.99 column B1 should be 2
if column A1 is between 6 and 6.99 column B1 should be 3
if column A1 is between 7 and 7.99 column B1 should be 4
if column A1 is above 8 column B1 should be 5

is there a way of doing this as I am stuck.

thanks Scott

[AliGW]

Welcome to the forum!

You need to cater for below 5. In B1 copied down:

=LOOKUP(A1,{0,5,6,7,8},{1,2,3,4,5})

[sandy666]

If column A1 is between 5 and 5.99 column B1 should be 2
if column A1 is between 6 and 6.99 column B1 should be 3
if column A1 is between 7 and 7.99 column B1 should be 4
if column A1 is above 8 column B1 should be 5

you can try also

Please Login or Register  to view this content.

[Mr Lynton]

Thanks AliGW

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.