I am trying to do a nested if and function to determine vacation days- I am basing it on if employee is FT and then referencing a cell that has the number of days they would get.

What is wrong with my formula - Please help!=IF(AND([Status]="FT",[Years Employed]>=4,$R$7,[Years Employed]=2<4,$R$8,[Years Employed]=1<2,$R$9,[Years Employed]>1,$R$10,))))

What do the [ ] around Status, etc. do? That is a structure I'm not familiar with. In any case, if you read the Excel Help files for the IF function, you'll see that a Nested IF has a number of actual IF's nested together. Your formula does not.

I'm also confused by the last Years Employed choice

I assume the first three choices are "4 or more years", "at least 2 but less than 4" and "at least 1 but less than 2."

However, your last choice is ">1". The other 3 choices already cover anything greater than 1. Did you mean <1?

If so, then maybe this is what you are looking for. The formula is split across 2 lines to make it easier to read.

=IF(A1="FT",IF(B1>=4,$R$7,IF(AND(B1>=2,B1<4),$R$8,

IF(AND(B1>=1,B1<2),$R$9,$R$10))),"Not Full Time")

The brackets are a function in 07 where you choose the Column title to format to all the records in that column. I tried this and it didn't work. I went ahead and used the first cell - It calculated wrong but at least it gave me an answer. Any other suggestions? I am getting desperate and frustrated.

=IF(B6="FT",IF(D6>=4,$R$7,IF(AND(D6>=2,D6<4,),$R$8,IF(AND(D6>=1,D6<2),$R$9,$R$10))),"Not Full Time")

D6 is Yrs emp(loyeed)

Name Status Date Hired Yrs emp Vacation Leave

Abba FT 8/23/2007 2.9 0

Abbot FT 5/7/2010 0.2 0

Adtkins FT 10/6/2008 1.7 5

Andrews FT 5/15/2008 2.1 0

Baker PT 3/19/2009 1.3 Not Full Time

Berg FT 12/28/2006 3.5 0

Bergeman FT 10/1/2009 0.7 0

Blake CN 8/17/2007 2.9 Not Full Time

Yrs of Employment Days

4 yrs or more 15 15 is in R7

2 and less than 4 10 10 is in R8

1 and less than 2 5 5 is in R9

less than 1 0

Reply: (edit)The brackets are a function in 07 where you choose the Column title to format to all the records in that column. I tried this and it didn't work. I went ahead and used the first cell - It calculated wrong but at least it gave me an answer. Any other suggestions? I am getting desperate and frustrated.

=IF(B6="FT",IF(D6>=4,$R$7,IF(AND(D6>=2,D6<4,),$R$8,IF(AND(D6>=1,D6<2),$R$9,$R$10))),"Not Full Time")

D6 is Yrs emp(loyeed)

Name Status Date Hired Yrs emp Vacation Leave

Abba FT 8/23/2007 2.9 0

Abbot FT 5/7/2010 0.2 0

Adtkins FT 10/6/2008 1.7 5

Andrews FT 5/15/2008 2.1 0

Baker PT 3/19/2009 1.3 Not Full Time

Berg FT 12/28/2006 3.5 0

Bergeman FT 10/1/2009 0.7 0

Blake CN 8/17/2007 2.9 Not Full TimeYrs of Employment Days

4 yrs or more 15 15 is in R7

2 and less than 4 10 10 is in R8

1 and less than 2 5 5 is in R9

less than 1 0

I don't have 2007, so I can't test the Structured Reference feature, but I believe that the problem with your formula is in this section: IF(AND(D6>=2,D6<4,)

The extra comma after the 4 is going to cause the AND to always evaluate to FALSE. I dropped that comma and got:

Abba FT 08/23/07 2.9 10 Abbot FT 05/07/10 0.2 0 Adtkins FT 10/06/08 1.7 5 Andrews FT 05/15/08 2.1 10 Baker PT 03/19/09 1.3 Not Full Time Berg FT 12/28/06 3.5 10 Bergema FT 10/01/09 0.7 0 Blake CN 08/17/07 2.9 Not Full Time

Thank you sooooooo much for your help. I will be back. You are great. Thanks again.

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History