# Nested if and in excel

Microsoft Office 2007 professional (aca...
February 13, 2010 at 12:13:51
Specs: Windows XP
 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,))))

See More: Nested if and in excel

#1
February 13, 2010 at 12:56:59
 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 choiceI 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")

Report •

#2
February 13, 2010 at 13:41:10
 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 LeaveAbba FT 8/23/2007 2.9 0Abbot FT 5/7/2010 0.2 0Adtkins FT 10/6/2008 1.7 5Andrews FT 5/15/2008 2.1 0Baker PT 3/19/2009 1.3 Not Full TimeBerg FT 12/28/2006 3.5 0Bergeman FT 10/1/2009 0.7 0Blake CN 8/17/2007 2.9 Not Full Time Yrs of Employment Days4 yrs or more 15 15 is in R72 and less than 4 10 10 is in R81 and less than 2 5 5 is in R9less than 1 0

Report •

#3
February 13, 2010 at 13:58:20
 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 LeaveAbba FT 8/23/2007 2.9 0Abbot FT 5/7/2010 0.2 0Adtkins FT 10/6/2008 1.7 5Andrews FT 5/15/2008 2.1 0Baker PT 3/19/2009 1.3 Not Full TimeBerg FT 12/28/2006 3.5 0Bergeman FT 10/1/2009 0.7 0Blake CN 8/17/2007 2.9 Not Full TimeYrs of Employment Days4 yrs or more 15 15 is in R72 and less than 4 10 10 is in R81 and less than 2 5 5 is in R9less than 1 0

Report •

Related Solutions

#4
February 13, 2010 at 14:35:13
 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 ```

Report •

#5
February 13, 2010 at 15:34:57
 Thank you sooooooo much for your help. I will be back. You are great.Thanks again.

Report •