IF/THEN/AND Formulas

[juliehmac]

Hi, I have three individual statements that I am trying to put together. They all work individually, but when I combine them it returns #VALUE!. I am trying to calculate accrual rates for personal leave based on the number of years at the company.

Anyone know what I need to do to correct it?

=IF(AND(C5>=0,C5<5),5)

=IF(AND(C5>=5,C5<10),6.67)

=IF(C5>=10,8.355)

=(IF(AND(C2>=0,C2<5),5), IF(AND(C2>=5,C2<10),6.67), IF(C2>10,8.355)) --- this returns #VALUE!

 

[Snakehips]

juliehmac,

Perhaps try..... =LOOKUP(C5,{0,5,10},{5,6.77,8.355})

Hope that helps.

Or with if statements maybe.... =IF(C5>9,8.355,IF(C5>4,6.67,5))
NB testing the higher values first.
Your limits in your combined formula are not the same as the individuals ??

 

[pbornemeier]

=IF(AND(C5>0,C5<5),5,IF(AND(C5>=5,C5<10),6.67,IF(C5>=10,8.355,"")))

or

=VLOOKUP(C5,{0,5;5,6.67;10,8.355},2,TRUE)

 

[juliehmac]

=IF(AND(C5>0,C5<5),5,IF(AND(C5>=5,C5<10),6.67,IF(C5>=10,8.355,"")))

or

=VLOOKUP(C5,{0,5;5,6.67;10,8.355},2,TRUE)

I really appreciate the help, but they don't appear to work I have a worksheet where column C is the years of service. I am trying to calculate the accrual rate based on the years of service.

If you work 1-4 years the rate is 5, if you work 5-9 years the rate is 6.67, and if you work 10 or more years the rate is 8.355.

I tried the three formulas for an employee with 16 years of service, and it keeps returning 5, so something appears to be off.

Please let me know if you need other information.

 

[juliehmac]

Actually it does work! sorry for the confusion! I had taken all of the formulas and pasted them into cell C2, which is why it didn't work.

Thank you again for the help!