Forum Discussion
IFS function not returning a result
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
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")as a variant, you can create an Age Group Table:
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.
I added 09-14 in the table but didn't have to change the formula:
cheers
=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.
