SOLVED

# Excel formula

Highlighted
Occasional Contributor

# Excel formula

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

12 Replies
Highlighted
Best Response confirmed by Johancharmy (Occasional Contributor)
Solution

# Re: Excel formula

=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

Highlighted

# Re: Excel formula

Thanks for coming back so soon. Doest seem to work !!!i have attached the error

Highlighted

# Re: Excel formula

seems like have made an error in number of brackets...
can u pls send screenshot of the formula in your worksheet?

Highlighted

# Re: Excel formula

Thankyou my friend

Highlighted

# Re: Excel formula

Is it possible for you to share the file?

Because it works perfectly in my system, refer below:

Highlighted

# Re: Excel formula

The formula must be in i5 and musts reference to h5

Highlighted

# Re: Excel formula

@Johancharmy solution attached.

I have added an extra column AB, I hope that is ok.

Highlighted

# Re: Excel formula

Will you send the solution back to me?

Highlighted

# Re: Excel formula

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.

Highlighted

# Re: Excel formula

AWESOME my FRIEND!  Thank you so much!

Appreciate all your help so much!

Johan

Highlighted

# Re: Excel formula

Thank you so much for all the help my friend!

Appreciate it SO MUCH!

Johan

Highlighted

# Re: Excel formula

yes, attached above.