IFS function not returning a result

Occasional Visitor

I am an Office 365 subscriber and I am using Excel version 2109 build 14430.20298. 

 

I am new to the IFS function but it has worked in the same spreadsheet in other formulas.  But in this one, I have been unable to get it to return a result.

 

Column L contains ages in years, formatted as numbers.  In column M, I want to create age groups.  The IFS formula I have used is:

 

=IFS(L2>14<25,"15-24 years",L2>24<35,"25-34 years",L2>34<45,"35-44 years",L2>44<55,"45-54 years",L2>54<65,"55-64 years",L2>64,"65+ years")

 

Initially, this formula returned an "#N/A" result.  Then out of the blue for no apparent reason, it just stopped working at all and the formula appears in the cell as text.  I have changed the format of column M to text, back to number, to general and none of it makes a difference.  Calculation options are set to Automatic, the worksheet is not protected. 

 

Can anyone help with this, please?

 

3 Replies

@lpea60 

=IFS(AND(L2>14,L2<25),"15-24 years",

AND(L2>24,L2<35),"25-34 years",

AND(L2>34,L2<45),"35-44 years",

AND(L2>44,L2<55),"45-54 years",

AND(L2>54,L2<65),"55-64 years",

L2>64,"65+ years")

 

This formula returns the values for the different ages.

@lpea60 

 

as a variant, you can create an Age Group Table:

Yea_So_0-1634364170283.png

and use this formula:

=IFERROR(INDEX($O$2:$O$7,MATCH(L2,VALUE(LEFT($O$2:$O$7,2)),1),0),"Under 15")

and even if you change the Age Group table, the above formula will dynamically adapt to the table.

Yea_So_1-1634364316158.png

I added 09-14 in the table but didn't have to change the formula:

Yea_So_2-1634364463740.png

 

cheers

 

@lpea60 

As variant

=IFNA(
  LOOKUP( L2,
    {14,25,35,45,55,65},
    {"15-24 years","25-34 years","35-44 years","45-54 years","55-64 years","65+ years"} ),
 "too young")