May 11 2020 12:32 AM
I have 7 criteria ages groups, 0-1 Years, 2-4 Years, 5-12 Years, 13-18 Years, 19-30 Years, 31-59 Years, and 59+ Years. (1) Infants, (2) Toddlers, (3) Juniors, (4) Youth, (5) Y-Adults, (6) Adults, and (7) Seniors. I want a formula that will automatically add the criteria to the age. pls help! Johan
May 11 2020 12:52 AM
Solution=IF(A1>59,"Seniors", IF(A1>30,"Adults", IF(A1>18,"Y-Adults", IF(A1>12,"Youth", IF(A1>4,"Juniors", IF(A1>1,"Toddlers", "Infants"))))))
Note: Change 'A1' to relevant reference cell in your worksheet
May 11 2020 01:11 AM
Thanks for coming back so soon. Doest seem to work !!!i have attached the error
May 11 2020 01:28 AM
seems like have made an error in number of brackets...
can u pls send screenshot of the formula in your worksheet?
May 11 2020 01:36 AM
Is it possible for you to share the file?
Because it works perfectly in my system, refer below:
May 11 2020 01:41 AM
The formula must be in i5 and musts reference to h5
May 11 2020 01:51 AM
@Johancharmy solution attached.
I have added an extra column AB, I hope that is ok.
May 11 2020 02:13 AM
I'd clean the formulas in the table a bit to avoid errors
Code:
=IFNA(VLOOKUP([@[Name & Surname]],'Member Codes'!$A$2:$B$1087,2,FALSE),"")
Age:
=IF([@[B-Date]]>1,ROUNDDOWN(YEARFRAC([@[B-Date]],TODAY(),1),0),"")
Profile:
=IFNA(LOOKUP([@AGE],$AD$7:$AD$13,$AB$7:$AB$13),"")
In stats cells on the top to avoid hardcoding, e.g. for Male
=COUNTIF(Table1[Geslag],D$1)
for Infants
=COUNTIF(Table1[[PROFILE]:[PROFILE]],J$1)
and similar for the rest.
May 11 2020 02:14 AM
AWESOME my FRIEND! Thank you so much!
Appreciate all your help so much!
Johan
May 11 2020 02:20 AM
Thank you so much for all the help my friend!
Appreciate it SO MUCH!
Johan
May 11 2020 12:52 AM
Solution=IF(A1>59,"Seniors", IF(A1>30,"Adults", IF(A1>18,"Y-Adults", IF(A1>12,"Youth", IF(A1>4,"Juniors", IF(A1>1,"Toddlers", "Infants"))))))
Note: Change 'A1' to relevant reference cell in your worksheet