IF statement too long

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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
 
Correction:
You can combine:

=IF(ISBLANK($F$5)," ",IF(F6<2," ",........

Into:

=IF(AND(F5="",F6<2),"",................
Click to expand...

Make that:

=IF(OR(F5="",F6<2),"",................

Biff
 
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
 
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
 
Back
Top