Age grouping

[shaniwafa]

Hi,
I have stuck up while trying to create a formula in my family forum database work sheet, which has a DOB column in C5 to get age in D5. There is one more column to segregate different age group in E5. If I wish to add one more column for gender to get result by considering new column, how the existing formula need to edit if I choose 3 more age group for female as CAT-5, CAT-6, CAT-7.
Formula currently used in the sheet


[FONT=Helvetica Neue, Helvetica, Arial, sans-serif]INT(YEARFRAC(C5,TODAY())) - for age
[FONT=Helvetica Neue, Helvetica, Arial, sans-serif]
[/FONT]
[FONT=Helvetica Neue, Helvetica, Arial, sans-serif]LOOKUP(D5,{0;6;15;40},{"CAT-1"<wbr>;"CAT-2";"CAT-3";"CAT-4"}) - for age group
[/FONT]
[FONT=Helvetica Neue, Helvetica, Arial, sans-serif][/FONT]
Hope someone can suggest an easy approach to achieve this result.
Any help would be greatly appreciated
Regards

Shaniwafa
[/FONT]

 

[admiral100]

Hi,

Try this : ("F" for female)

=IF(F5="M",LOOKUP(D5,{0;6;15;40},{"CAT-1";"CAT-2";"CAT-3";"CAT-4"}),LOOKUP(D5,{0;6;15;40},{"CAT-5";"CAT-6";"CAT-7";"CAT-8"}))