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

Report •


#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 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")


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 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


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 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



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 •

Ask Question