Happy Holidays male or female

sanch0156

New Member
Joined
Apr 13, 2016
Messages
8
Hello Everyone,

I created this sheet for the organization which I volunteer unfortunately I am
stuck! For those less fortunate families so we can provide them with gifts.

I would like it to detect automatically gender whether it be male of female.


Here are the codes I have so far:

MALE =IF(C121<6,"",IF(C121>=11,"",IF(C121>=6,"M",)))
FEMALE =IF($C124<=5,"F","")

Thank you in advance.
 
Try the following formula -- put it in cell D2 and copy across and down as needed:

=IF(OR($B2<--LEFT(D$1,SEARCH("-",D$1)-1),$B2>--MID(D$1,SEARCH("-",D$1)+1,255)),"",LEFT($C2))
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I was using this formula. Which only detects age. Let's use John for example I would put the formula below in F2 and as you can tell by the formula it would put an M at the end after it met all the criteria.
...

I would like to add a TEXT "F' or "M" in a cell for both age and gender.
Your specifications are ambiguous. It appears you want both the age and M/F in columns D:G. In that case -
For M/F before the age (e.g. M12), use:
=IF(MAX(INT(($B2-1)/5),0)-(COLUMN()-4)=0,LEFT($C2,1)&$B2,"")
For M/F after the age (e.g. 12M), use:
=IF(MAX(INT(($B2-1)/5),0)-(COLUMN()-4)=0,$B2&LEFT($C2,1),"")
To insert a space between the age & gender, replace '&' with '&" "&'
 
Last edited:
Upvote 0
Thank you for your patience. Let me see if I can clarify this for example: John who is 12 years old and is male. I would like your help with a formula in F2 to detect his age B2 (12) and his gender C2 (Male). I apologize for reiterating formulas should be placed in D:F After a process of elimination placing an M for male or an F for female in the appropriate column(D:F).

I am referring to the last worksheet I sent yesterday Dec. 8

Again thank you very much. Happy holidays to all in advance.
 
Upvote 0
Child's Name
Age
Birth
Gender
0 - 5
6 - 10
11 - 15
John
12
10/8/2004
Male
M
Jane
5
Female
F

<tbody>
</tbody>

I wish I could include the actual sheet. B2 refers to C2 (10/8/2004). Formula inserted into B2 =DATEDIF(C2,TODAY(),"Y"). Which of course returns actual age into B2 (12).
 
Upvote 0
This is the first time you've mentioned a 'Birth' column. All the advice given so far has been predicated on your data being in columns A:C only. You can't expect the correct results when your actual setup differs so fundamentally from what you posted.

If your latest depiction is correct and all you want to appear in the age-range columns is M or F, you could use:
=IF(MAX(INT(($B2-1)/5),0)-(COLUMN()-5)=0,LEFT($D2,1),"")
in E2. Copy that across and down as far as needed.
 
Upvote 0
Why don't you just create 3 simple formulas and then copy them down.

Code:
[TABLE="width: 357"]
<tbody>[TR]
  [TD="class: xl66, width: 357"]E2 =IF(AND($B2>=1,$B2<=5),LEFT($D2,1),"")
[TABLE="width: 367"]
<tbody>[TR]
  [TD="class: xl66, width: 367"]F2=IF(AND($B2>=6,$B2<=10),LEFT($D2,1),"")
[TABLE="width: 70"]
<tbody>[TR]
  [TD="class: xl66, width: 70"]G2=IF(AND($B2>=11,$B2<=15),LEFT($D2,1),"")[/TD]
[/TR]
</tbody>[/TABLE]

Sunny[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Paul
I guess it is just a matter of preference.
I would normally use simple formulas (that most users know) than a complicated one that I (or the users) may not remember how it work many months down the line.
The formulas may be easy for the experts but unfortunately I am not one of them :(.

Cheers and Merry Christmas.
Sunny
 
Upvote 0
The formula(s) works! Again I would like to thank those of you who had the patience to assist me. No way I would've figured this out on my own.
I want to wish everyone in the group a happy and memorable holiday. :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top