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 SpaceX laying off some of its workforce will help it succeed?

Discuss in The Lounge

Poll History