| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ID number to Gender
I want to see if the employee is a male or female from the ID number?
ID number - 751105 0 931087 The SEVENTH number will tell you if the employee is a male or female 0-4 = Female 5-9 = Male What formule can I use? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ID number to Gender
Try
=IF(--MID(A1,7,1)<5,"Female","Male") or to handle errors & blank entries =IF(AND(N(A1),LEN(A1)6),IF(--MID(A1,7,1)<5,"Female","Male"),"") -- Jacob (MVP - Excel) "Mags" wrote: I want to see if the employee is a male or female from the ID number? ID number - 751105 0 931087 The SEVENTH number will tell you if the employee is a male or female 0-4 = Female 5-9 = Male What formule can I use? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ID number to Gender
Hi Jacob,
May i know what is the purpose of putting -- in front of MID and what does it mean? My formula is quite similar to yours =IF((MID(A1,7,1)<="4"),"Female","Male") but i need to put "" at the number 4, though I do not know why I need to do so. "Jacob Skaria" wrote: Try =IF(--MID(A1,7,1)<5,"Female","Male") or to handle errors & blank entries =IF(AND(N(A1),LEN(A1)6),IF(--MID(A1,7,1)<5,"Female","Male"),"") -- Jacob (MVP - Excel) "Mags" wrote: I want to see if the employee is a male or female from the ID number? ID number - 751105 0 931087 The SEVENTH number will tell you if the employee is a male or female 0-4 = Female 5-9 = Male What formule can I use? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ID number to Gender
The formula I posted compares the MID() result with a numeric as below (<5).
MID() returns a string value and hence I have used -- to convert that to a numeric MID(A1,7,1)<5 Your formula compares the MID() result with a string "4" (with double quotes) and hence there is no need to convert this to a numeric..Instead if you have used the numeric 4 (without quotes) as below...the formula will not work correctly...You may try the below on several examples.. =IF((MID(A1,7,1)<=4),"Female","Male") -- Jacob (MVP - Excel) "ck13" wrote: Hi Jacob, May i know what is the purpose of putting -- in front of MID and what does it mean? My formula is quite similar to yours =IF((MID(A1,7,1)<="4"),"Female","Male") but i need to put "" at the number 4, though I do not know why I need to do so. "Jacob Skaria" wrote: Try =IF(--MID(A1,7,1)<5,"Female","Male") or to handle errors & blank entries =IF(AND(N(A1),LEN(A1)6),IF(--MID(A1,7,1)<5,"Female","Male"),"") -- Jacob (MVP - Excel) "Mags" wrote: I want to see if the employee is a male or female from the ID number? ID number - 751105 0 931087 The SEVENTH number will tell you if the employee is a male or female 0-4 = Female 5-9 = Male What formule can I use? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ID number to Gender
Assume that you are having the value in A1 cell
A1 Cell 751105 0 931087 Copy and paste the below formula in B1 cell. =IF(A1="","",IF(AND(VALUE(MID(TRIM(A1),8,1))=0,VA LUE(MID(TRIM(A1),8,1))<=4),"Female",IF(AND(VALUE(M ID(TRIM(A1),8,1))=5,VALUE(MID(TRIM(A1),8,1))<=9), "Male",""))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Mags" wrote: I want to see if the employee is a male or female from the ID number? ID number - 751105 0 931087 The SEVENTH number will tell you if the employee is a male or female 0-4 = Female 5-9 = Male What formule can I use? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ID number to Gender
Thanks it works
"Ms-Exl-Learner" wrote: Assume that you are having the value in A1 cell A1 Cell 751105 0 931087 Copy and paste the below formula in B1 cell. =IF(A1="","",IF(AND(VALUE(MID(TRIM(A1),8,1))=0,VA LUE(MID(TRIM(A1),8,1))<=4),"Female",IF(AND(VALUE(M ID(TRIM(A1),8,1))=5,VALUE(MID(TRIM(A1),8,1))<=9), "Male",""))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Mags" wrote: I want to see if the employee is a male or female from the ID number? ID number - 751105 0 931087 The SEVENTH number will tell you if the employee is a male or female 0-4 = Female 5-9 = Male What formule can I use? |
|
#7
|
|||
|
|||
Quote:
copy this formulae in C1 =IF(N(RIGHT(LEFT(B1,7),1))<6,"Female","Male") all the best
__________________
Thanks Bala |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ID number to Gender
On Thursday, 20 May 2010 16:14:01 UTC+8, Mags wrote:
I want to see if the employee is a male or female from the ID number? ID number - 75110501310 8 The TWELVETH number will tell you if the employee is a male or female EVEN NUMBER = Female ODD NUMBER = Male What formule can I use? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ID number to Gender
On Wed, 21 May 2014 23:42:20 -0700 (PDT), wrote:
On Thursday, 20 May 2010 16:14:01 UTC+8, Mags wrote: I want to see if the employee is a male or female from the ID number? ID number - 75110501310 8 The TWELVETH number will tell you if the employee is a male or female EVEN NUMBER = Female ODD NUMBER = Male What formule can I use? Do you want gender or sex? Male and Female are sexes, not genders. Genders are masculine, feminine, neuter and trans, and perhaps a few orther things as well. -- Steve Hayes from Tshwane, South Africa Web: http://www.khanya.org.za/stevesig.htm Blog: http://khanya.wordpress.com E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can Excel recognize gender? | Excel Worksheet Functions | |||
| how do i graph gender & age in in a line graph? | Charts and Charting in Excel | |||
| Countif Miltiple Criteria - Age Range and Gender | Excel Discussion (Misc queries) | |||
| How do I get gender, race, age percentages from my spreadsheet? | Excel Worksheet Functions | |||
| Count age and gender records from two different columns | Excel Worksheet Functions | |||
Linear Mode
