mtrout
asked on
Trying to correct if then else statement in Excel 2007
Hello,
Would really appreciate assistance please.
I am trying to correct the following formula in a spread sheet using Excel 2007:
=IF(AND(E2>$I$3,E2<=$J$3), ”Spring 2011”,IF(AND(E2>=$I$4,E2<= $J$4),"Sum mer 2011",IF(AND(E2>=$I$5,E2<$ J$5),"Fall 2011",IF(AND(E2>=$I$6,E2<= $J$6),"Spr ing 2012",IF(AND(E2>=$I$7,E2<= $J$7),"Sum mer 2012",IF(AND(E2>=$I$8,E2<= $J$8),"Fal l 2012","From a Semester prior to 2011"))))))
the syntax checks out, but ai keep getting the last statement "From a Semester prior to 2011" in each and every cell.
I should not be getting: "From a Semester prior to 2011" at all. all dates in column E are in 2011 or 2012.
My date column and column I and J have the same format of date
This is what I have set up in columns I and J:
row I J
3 01/01/2011 05/31/2011
4 06/01/2011 07/31/2011
5 08/01/2011 12/31/2011
6 01/01/2012 05/31/2012
7 06/01/2012 07/31/2012
8 08/01/2012 12/31/2012
Date data in E2 through E10
02/01/2011
04/19/2011
01/03/2011
02/28/2011
03/28/2011
04/18/2011
05/02/2011
05/16/2011
06/06/2011
The formula above, is written in column H which is blank, but the way the formula is written now, it prints: "From a Semester prior to 2011. Thank you for your assistance.
Would really appreciate assistance please.
I am trying to correct the following formula in a spread sheet using Excel 2007:
=IF(AND(E2>$I$3,E2<=$J$3),
the syntax checks out, but ai keep getting the last statement "From a Semester prior to 2011" in each and every cell.
I should not be getting: "From a Semester prior to 2011" at all. all dates in column E are in 2011 or 2012.
My date column and column I and J have the same format of date
This is what I have set up in columns I and J:
row I J
3 01/01/2011 05/31/2011
4 06/01/2011 07/31/2011
5 08/01/2011 12/31/2011
6 01/01/2012 05/31/2012
7 06/01/2012 07/31/2012
8 08/01/2012 12/31/2012
Date data in E2 through E10
02/01/2011
04/19/2011
01/03/2011
02/28/2011
03/28/2011
04/18/2011
05/02/2011
05/16/2011
06/06/2011
The formula above, is written in column H which is blank, but the way the formula is written now, it prints: "From a Semester prior to 2011. Thank you for your assistance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Both OCDan and berryhoudinifor the responses. The one you corrected for me OCDan worked best for me and I can understand it. Thank you both again.
ASKER
Question for you OCDan, Please. I should have tested the formula in my spreadsheet before responding. It did not work. I checked the data type, seems like it's date to me. What else could be wrong? Other than a column change, the formula seem the same. What have I done incorrectly? File (brief version) attached. Thank you so much. I can open a new call if that is needed, just let me know.
ExampleFileSemester.xls
ExampleFileSemester.xls
=IFERROR(LOOKUP(E3,H$3:J$8
regards, barry
Date-LOOKUP.xlsx