| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
IF statement too long
I have a cell with the following:
=IF(ISBLANK($F$5)," ",IF(F6<2," ",IF(F5=2000,'(4)Commercial Savings'!D16,IF(F5=2001,'(4)Commercial Savings'!D21,IF(F5=2002,'(4)Commercial Savings'!D26,IF($F$5=2003,'(4)Commercial Savings'!D31,IF($F$5=2004,'(4)Commercial Savings'!D36,IF($F$5=2005,'(4)Commercial Savings'!D41)))))))) I am trying to add another if for the year 2006, but I get an error, I think there is a limit to the number of IF's if so, how do I program this? Thanks, -- KSH |
|
#2
|
|||
|
|||
|
IF statement too long
Hi!
You can combine: =IF(ISBLANK($F$5)," ",IF(F6<2," ",........ Into: =IF(AND(F5="",F6<2),"",................ That'll allow you to add 1 more nested IF for 2006. However, your best bet would be to create a table and do a lookup. Biff "KSH" wrote in message ... I have a cell with the following: =IF(ISBLANK($F$5)," ",IF(F6<2," ",IF(F5=2000,'(4)Commercial Savings'!D16,IF(F5=2001,'(4)Commercial Savings'!D21,IF(F5=2002,'(4)Commercial Savings'!D26,IF($F$5=2003,'(4)Commercial Savings'!D31,IF($F$5=2004,'(4)Commercial Savings'!D36,IF($F$5=2005,'(4)Commercial Savings'!D41)))))))) I am trying to add another if for the year 2006, but I get an error, I think there is a limit to the number of IF's if so, how do I program this? Thanks, -- KSH |
|
#3
|
|||
|
|||
|
IF statement too long
Correction:
You can combine: =IF(ISBLANK($F$5)," ",IF(F6<2," ",........ Into: =IF(AND(F5="",F6<2),"",................ Make that: =IF(OR(F5="",F6<2),"",................ Biff "Biff" wrote in message ... Hi! You can combine: =IF(ISBLANK($F$5)," ",IF(F6<2," ",........ Into: =IF(AND(F5="",F6<2),"",................ That'll allow you to add 1 more nested IF for 2006. However, your best bet would be to create a table and do a lookup. Biff "KSH" wrote in message ... I have a cell with the following: =IF(ISBLANK($F$5)," ",IF(F6<2," ",IF(F5=2000,'(4)Commercial Savings'!D16,IF(F5=2001,'(4)Commercial Savings'!D21,IF(F5=2002,'(4)Commercial Savings'!D26,IF($F$5=2003,'(4)Commercial Savings'!D31,IF($F$5=2004,'(4)Commercial Savings'!D36,IF($F$5=2005,'(4)Commercial Savings'!D41)))))))) I am trying to add another if for the year 2006, but I get an error, I think there is a limit to the number of IF's if so, how do I program this? Thanks, -- KSH |
|
#4
|
|||
|
|||
|
IF statement too long
Biff,
Thanks that will do the trick. -- KSH "Biff" wrote: Correction: You can combine: =IF(ISBLANK($F$5)," ",IF(F6<2," ",........ Into: =IF(AND(F5="",F6<2),"",................ Make that: =IF(OR(F5="",F6<2),"",................ Biff "Biff" wrote in message ... Hi! You can combine: =IF(ISBLANK($F$5)," ",IF(F6<2," ",........ Into: =IF(AND(F5="",F6<2),"",................ That'll allow you to add 1 more nested IF for 2006. However, your best bet would be to create a table and do a lookup. Biff "KSH" wrote in message ... I have a cell with the following: =IF(ISBLANK($F$5)," ",IF(F6<2," ",IF(F5=2000,'(4)Commercial Savings'!D16,IF(F5=2001,'(4)Commercial Savings'!D21,IF(F5=2002,'(4)Commercial Savings'!D26,IF($F$5=2003,'(4)Commercial Savings'!D31,IF($F$5=2004,'(4)Commercial Savings'!D36,IF($F$5=2005,'(4)Commercial Savings'!D41)))))))) I am trying to add another if for the year 2006, but I get an error, I think there is a limit to the number of IF's if so, how do I program this? Thanks, -- KSH |
|
#5
|
|||
|
|||
|
IF statement too long
Hi
You could use a single IF statement and use the fact that you are incrementing the cell row number by 5 for each additional year. =IF(OR(ISBLANK($F$5),F6<2),"",INDIRECT("'(4) Commercial Savings'!D"&(16+5*RIGHT(F5,2))) That will see you through to the end of the century<g Regards Roger Govier KSH wrote: I have a cell with the following: =IF(ISBLANK($F$5)," ",IF(F6<2," ",IF(F5=2000,'(4)Commercial Savings'!D16,IF(F5=2001,'(4)Commercial Savings'!D21,IF(F5=2002,'(4)Commercial Savings'!D26,IF($F$5=2003,'(4)Commercial Savings'!D31,IF($F$5=2004,'(4)Commercial Savings'!D36,IF($F$5=2005,'(4)Commercial Savings'!D41)))))))) I am trying to add another if for the year 2006, but I get an error, I think there is a limit to the number of IF's if so, how do I program this? Thanks, |
|
#6
|
|||
|
|||
|
IF statement too long
The first increment is 10 and the others are 5.
Although you could shorten the original formula considerably by using a separate IF for the first increment, I would still use a lookup. Biff "Roger Govier" wrote in message ... Hi You could use a single IF statement and use the fact that you are incrementing the cell row number by 5 for each additional year. =IF(OR(ISBLANK($F$5),F6<2),"",INDIRECT("'(4) Commercial Savings'!D"&(16+5*RIGHT(F5,2))) That will see you through to the end of the century<g Regards Roger Govier KSH wrote: I have a cell with the following: =IF(ISBLANK($F$5)," ",IF(F6<2," ",IF(F5=2000,'(4)Commercial Savings'!D16,IF(F5=2001,'(4)Commercial Savings'!D21,IF(F5=2002,'(4)Commercial Savings'!D26,IF($F$5=2003,'(4)Commercial Savings'!D31,IF($F$5=2004,'(4)Commercial Savings'!D36,IF($F$5=2005,'(4)Commercial Savings'!D41)))))))) I am trying to add another if for the year 2006, but I get an error, I think there is a limit to the number of IF's if so, how do I program this? Thanks, |
|
#7
|
|||
|
|||
|
IF statement too long
ooops!
Disregard: The first increment is 10 and the others are 5. Biff "Biff" wrote in message ... The first increment is 10 and the others are 5. Although you could shorten the original formula considerably by using a separate IF for the first increment, I would still use a lookup. Biff "Roger Govier" wrote in message ... Hi You could use a single IF statement and use the fact that you are incrementing the cell row number by 5 for each additional year. =IF(OR(ISBLANK($F$5),F6<2),"",INDIRECT("'(4) Commercial Savings'!D"&(16+5*RIGHT(F5,2))) That will see you through to the end of the century<g Regards Roger Govier KSH wrote: I have a cell with the following: =IF(ISBLANK($F$5)," ",IF(F6<2," ",IF(F5=2000,'(4)Commercial Savings'!D16,IF(F5=2001,'(4)Commercial Savings'!D21,IF(F5=2002,'(4)Commercial Savings'!D26,IF($F$5=2003,'(4)Commercial Savings'!D31,IF($F$5=2004,'(4)Commercial Savings'!D36,IF($F$5=2005,'(4)Commercial Savings'!D41)))))))) I am trying to add another if for the year 2006, but I get an error, I think there is a limit to the number of IF's if so, how do I program this? Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Long IF Statement | Excel Discussion (Misc queries) | |||
| more than 3 conditions in conditional formatting - possible? | Excel Discussion (Misc queries) | |||
| Date & Time | New Users to Excel | |||
| Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
| clock | Excel Worksheet Functions | |||
Linear Mode
