Nested IF Formula from IFS

[Justijb]

Hello,

Might be an easy fix for you Excel experts but I can't seem to get the nested IF formula to return the results I need. The formula would look at number of days and return text as below:

Convert the following IFS formula:

IFS($AL13<=365,"Within One Year",AND($AL13>365,$AL13<=730),"Within Two Years",AND($AL13>730,$AL13<=1095),"Within Three Years",AND($AL13>1095,$AL13<=1825),"Within Five Years",$AL13>1825,"More than Five Years")

to a nested IF formula (tried):

=IF($AL13<=365,"Within One Year",IF($AL13>365,$AL13<=730,"Within Two Years",IF($AL13>730,$AL13<=1095,"Within Three Years",IF($AL13>1095,$AL13<=1825,"Within Five Years",IF($AL13>1825,"More than Five Years")))))

Cell AL13 would be a calculation of dates with return/output in count of days. Tried using AND & IF function after IF statement, which works but only gives me the negative values as "Within One Year" correctly, remaining results are #VALUE

Please advise.

Thank you.

-J

 

[Fluff]

How about
=IF($AL13<=365,"Within One Year",IF($AL13<=730,"Within Two Years",IF($AL13<=1095,"Within Three Years",IF($AL13<=1825,"Within Five Years","More than Five Years"))))

 

[tyija1995]

Hey try this:

IF($AL13=0,"",IFERROR(CHOOSE(ROUNDUP($AL13/365,0),"Within One Year","Within Two Years","Within Three Years","Within Five Years","Within Five Years"),"More than Five Years"))

 

[DanteAmor]

Try this:

IF(AL13<=365,"Within One Year",IF(AL13>1825,"More than Five","Within "&LOOKUP(AL13,{366,731,1096,1826},{"Two","Three","Five"}))&" Years")

 

[tyija1995]

Try this:

IF(AL13<=365,"Within One Year",IF(AL13>1825,"More than Five","Within "&LOOKUP(AL13,{366,731,1096,1826},{"Two","Three","Five"}))&" Years")

Nice use of Lookup!

 

[DanteAmor]

Thanks @tyija1995

 

[DanteAmor]

Try this:
IF(AL13<=365,"Within One Year",IF(AL13>1825,"More than Five","Within "&LOOKUP(AL13,{366,731,1096,1826},{"Two","Three","Five"}))&" Years")

I put an unnecessary value, it should be like this:

=IF(AL13<=365,"Within One Year",IF(AL13>1825,"More than Five","Within "&LOOKUP(AL13,{366,731,1096},{"Two","Three","Five"}))&" Years")