nested IF statement

[nigec]

Hi,

Please help, I need to nest the following 3 IFs:

=IF(L3=1,"n/a")
=IF(L3=2, F4+28)
=IF(L3=3, F4+182)

Thanks
nigec

[TMS]

=IF(L3=1,"n/a",IF(L3=2, F4+28,IF(L3=3, F4+182,"")))


Regards, TMS

[FDibbins]

Use this if there will only ever be 3 values in L3
=IF(L3=1,"n/a",IF(L3=2, F4+28,F4+182))
otherwise use this...
=IF(L3=1,"n/a",IF(L3=2, F4+28,IF(L3=3,F4+182,""))))

[nigec]

Thanks Guys! 1 more question please, if I wanted to add 6 months to a date (i.e. Feb 28th to Aug 28th, rather than IF(L3=3,F4+182,"") how would I do this?

Thanks again

[Spitzerpl]

You could also use the following which may be cleaner
=choose(L3,"n/a",F4+28,F4+182)

If L3 can equal 4 down the road you can simply add a fourth comma rather then further nesting an if statement.

[TMS]

=DATE(YEAR(F4),MONTH(F4)+6,DAY(F4))


Regards, TMS

[Spitzerpl]

Try date(year(f4),month(F4)+6,day(F4))

I can't try this out right now but the problem I see if the the month is after June you will get into the month being 13. I'm not sure what excel does with that but you could try the following.
date(year(f4)+if(month(f4)>6,1,0),choose(month(f4),7,8,9,10,11,12,1,2,3,4,5,6),day(f4))

[TMS]

@Spitzerpl: I'm pretty sure it rolls the year over.

See http://office.microsoft.com/en-gb/ex...010062283.aspx


Regards, TMS

[Spitzerpl]

It sure does. Good to know. Thanks!