SOLVED

Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1379006%22%20slang%3D%22en-US%22%3EExcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%207%20criteria%20ages%20groups%2C%200-1%20Years%2C%202-4%20Years%2C%205-12%20Years%2C%2013-18%20Years%2C%2019-30%20Years%2C%2031-59%20Years%2C%20and%2059%2B%20Years.%20(1)%20Infants%2C%20(2)%20Toddlers%2C%20(3)%20Juniors%2C%20(4)%20Youth%2C%20(5)%20Y-Adults%2C%20(6)%20Adults%2C%20and%20(7)%20Seniors.%20I%20want%20a%20formula%20that%20will%20automatically%20add%20the%20criteria%20to%20the%20age.%20pls%20help!%20Johan%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1379006%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1379053%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379053%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIF(A1%26gt%3B59%2C%22Seniors%22%2C%20IF(A1%26gt%3B30%2C%22Adults%22%2C%20IF(A1%26gt%3B18%2C%22Y-Adults%22%2C%20IF(A1%26gt%3B12%2C%22Youth%22%2C%20IF(A1%26gt%3B4%2C%22Juniors%22%2C%20IF(A1%26gt%3B1%2C%22Toddlers%22%2C%20%22Infants%22))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20Change%20'A1'%20to%20relevant%20reference%20cell%20in%20your%20worksheet%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1379110%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379110%22%20slang%3D%22en-US%22%3E%3CP%3Eseems%20like%20have%20made%20an%20error%20in%20number%20of%20brackets...%3CBR%20%2F%3Ecan%20u%20pls%20send%20screenshot%20of%20the%20formula%20in%20your%20worksheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1379083%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1379083%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20coming%20back%20so%20soon.%20Doest%20seem%20to%20work%20!!!i%20have%20attached%20the%20error%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

=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

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

Highlighted

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

Highlighted

Thankyou my friend

Highlighted

@Johancharmy 

Is it possible for you to share the file?

 

Because it works perfectly in my system, refer below:

bhushan_z_0-1589186120121.png

 

Highlighted

The formula must be in i5 and musts reference to h5

Highlighted

@Johancharmy solution attached.

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

Highlighted

Will you send the solution back to me?

Highlighted

@Johancharmy 

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

AWESOME my FRIEND!  Thank you so much! 

Appreciate all your help so much!

 

Johan

Highlighted

Thank you so much for all the help my friend!

 

Appreciate it SO MUCH!

 

Johan

Highlighted
yes, attached above.