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
Utf
5/20/2010 8:14:01 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
7732 Views

Similar Articles

[PageSpeed] 13

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?
0
Utf
5/20/2010 8:48:01 AM
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?
0
Utf
5/20/2010 8:56:01 AM
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?
0
Utf
5/20/2010 9:01:01 AM
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,VALUE(MID(TRIM(A1),8,1))<=4),"Female",IF(AND(VALUE(MID(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?
0
Utf
5/20/2010 9:10:01 AM
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,VALUE(MID(TRIM(A1),8,1))<=4),"Female",IF(AND(VALUE(MID(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?
2
Utf
5/20/2010 10:07:01 AM
Reply:

Similar Artilces:

numbering #4
Is there any system of 'outline numbering' in Excel? Hope i'm not asking for too much! -- Dr. Sachin Wagh MBBS, DHA, DPH Nothing as easy as the outlining within MSWord. Typing the numbers is one way (not very appealing). Using a formula based on the values in the other cells may work--if you can pickout something that would indicate the level--not very nice, either. Dr. Sachin Wagh wrote: > > Is there any system of 'outline numbering' in Excel? Hope i'm not asking for > too much! > -- > Dr. Sachin Wagh > MBBS, DHA, DPH -- Dave Peterson ...

Number Format Problem
I am importing data from a program at work. The data come in in th format of ###@##(three numbers, a letter, and then 2 more numbers). The problem is that when a code such as 123E04 is imported, exce thinks it means 1.23E+04. This is a problem becasue the data I need i the E04 part. (I use the RIGHT(cell,3) formula to get the portion want.) Any ideas on how to get the number in the format I want? have tried changing the cell characteristics, and all kinds of stuff. Thank -- kola556 ----------------------------------------------------------------------- kola5567's Profile: http://...

random number generator
hi there i don't know if this is the right section, however I was wondering is there some vba or formulae to generate random numbers in a certain range I would be very greatful if you can help me Thanks Owen -- manclad ------------------------------------------------------------------------ manclad's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30016 View this thread: http://www.excelforum.com/showthread.php?threadid=497074 I like J.E. McGimpsey's =RandInt(). http://www.mcgimpsey.com/excel/udfs/randint.html manclad wrote: > > hi there ...

event ID 9646
exchange 2003 sp2. got the event with " ... exceeded the maximum of 500 objects of type "objFolder". I found the article which expalined the symptom well. http://support.microsoft.com/kb/830829. But can someone explain how a mapi client uses the objects? What the function is for the objects, for example objFolder? In our case it's caused by the backup software sending out emails. How to control a mapi client not to open too many objects? thanks. On Wed, 6 Jan 2010 15:14:01 -0800, Chris <Chris@discussions.microsoft.com> wrote: >exchange 200...

Need trailing zeroes to show for range of numbers....
The file I pulled into Excel for some reason cut off 2 trailing zeroes, no matter how I format it they will not show up. There are thousands of them and I can't see typing in each one over just to get two zeroes on the end. Does anyone know of a way to format this so they will show up? I have searched Microsoft online with no help. Not too much information, but try this (after saving your workbook!) In an empty cell enter the numner 100. Edit>Copy Select your data Edit>Paste Special, check Multiply -- Kind Regards, Niek Otten Microsoft MVP - Excel "Sueshe" &...

user id or team id is missing
Thanks to everyone's help I've been able to import into the CDF/DMF successfully (or so I believe). However when weh I run the Migration tool, I receive for what appears to be every record: The user id or team id is missing. My users have licenses. I've run ListUmappedUsers and there are no unmapped users. I've run MapUser for all bad users previously. I'm not sure what this means and how to import my data. Please help. I've seen other similar posts, but no clear resolution. When you imported the records into the CDF did each record have an "owner" c...

Formula reads date as number
I have a formula that reads a list of dates in cells K5 through K20 an compares these dates to 1 date in cell M3. Cell M3 shows a date bu when I run the evaluate formula auditing tool on the formula, M3 date shows as a number, thus the formula fails. Any hel -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49423 Dates are stored as number of days since 1-1-1900. So today (dec 16,2005) is stored as...

Converting binary string to different whole number
Hi, I'm sure this is simple but am foggy today. I want to convert a string in a query as follows: 10001 to 1 01000 to 2 00100 to 3 00010 to 4 What function should I use? I thought of Switch([FIELD]="10001","1"). Any help would be greatly appreciated. Thanks in advance. OMS On Mon, 21 Jan 2008 10:51:17 -0600, "OMS" <ooigooee@dreamskillet.com.com> wrote: >Hi, > >I'm sure this is simple but am foggy today. I want to convert a string in a >query as follows: > >10001 to 1 >01000 to 2 >00100 to 3 >00010 to 4 > >...

count the number of new lows within a range of cells
Hello, Is there a formula or method to count the number of new lows within a range of cells? For example, the following are in col A rows 5 to 14. Within this set a new low has been reached 3 times- (96,95,94). What I would like is a formula or macro that that would produce the results - 3. There are about 9000 rows of data and I am looking for "count of new lows within the preceding 10 rows" on a rolling basis. Thanks. 100 101 96 97 98 97 95 97 94 95 Version 1: Use this if the 9000 or so rows don't have blanks in them. In this version you just need to select the first cel...

Restart numbering ater a set number of rows
I have an excel spreadsheet with 4 columns. the first two columns contains numbers which will never change the third column starts off with 0 (zero) and is repeated for 255 rows then on the 256 row it needs to change to 1 (so basically increment by 1 every 255 rows) the fourth column starts at 0 (zero) and counts up to 255 but then needs to reset it self back to 0 on the 256 row. Help Please!!!!!:confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ One way: I...

maximum number of indexes per SQL table that Access (jet) can deal with?
why is there a maximum number of indexes per SQL table that Access (jet) can deal with? is it really the count of indexes + statistics? I've seen plenty of documentation that states that typically DSS systems have twice as much index space as data space.. So I don't think that it's a case of 'over-indexing' I'm just tired of a crippled Jet front end and other people making me 'temporarily drop my indexes' so that they can link to my tables using Jet. has this bug gone away with Access 2007? Is it going away with Access 2010? ...

How to ignore records with a duplicate ID based on a value
I hae searched the forum tono avail, so I'll ask for help. I have a table [Scope Event Table] which records events with status changes. The table uses an auto numbered primary key (not shown in the example below). It has data like this: DR_ID Event_Date Reason 12556 01/03/2008 Added 12556 01/24/2008 Closed 12874 01/05/2008 Added 14128 02/09/2008 Added I am a novice at SQL. I am trying to build a query that would pull only the DR_ID’s where the most recent Reason = “Added”. So I would want results to look like this: DR_ID Event_Date Reason 12874 01/05/2008 Added 14128 02/09/2008...

Adding an apostrophe to a column of numbers
Hello, I need to add an apostrophe to each cell in a long column of numbers. I do not want to format them as text or format them as custom - our system requires the apostrophe at the beginning of the cell. My question is - is there a way to format one cell with the apostrophe and then copy that apostrophe down to the other cells?? or can someone write me something that will allow me to do it quickly? I want to avoid having to go into each cell and put the apostrophe in! Thank you in advance.... One way. Change 9 to your column Sub addapostophe() mc = 9 'column I For i = ...

How do I insert a specific number in a gantt bar
Hi I,m attempting to insert a specific number into a gantt bar or edit default text in the format box. Any body help? Hi GarryB, I'm not sure I understand completely what you are trying to do. I'm guessing edit the information which shows next to the bars on the Gantt chart? Insert a custom field into the table through Insert > Column and pick on of the text columns. Enter the value you want to show. Then Format > Bar Styles. Select the bar type and on the Text tab, select the custom field. I hope this helps. Let us know how you get along. Julie Pr...

Event ID 8197 Source: MSExchangeFBPublish
Hi Everyone, I am getting this Event every time I reboot my exchange server. Everything seems to come up ok but the server stalls on loading every time it is rebooted. The error description is: Error initializing session for virtual machine XXXXX. The error number is 0x8004011d. Make sure Microsoft Exchange Store is running. I don't know how to fix this. I did some searching on that error number and it pertained to Outlook but not the actual Exchange server. Is there a hotfix for this problem? Thanks, locoytravieso ...

Windows 2008 Server Kerberos Certificate error ID 29
Hello, I had a Windows 2000 Domain Controller, and translate all AD funtions to a new Windows 2008 server, PDC, GC. All worked fine, but a few days ago I got this error, ID 29, Kerberos-Key-Distribution-Center, not certificate found.... All users can´t access the network shares by the server name, but through the IP we managed to restore the network units. Is there a way to restore the certificate , without regenerate the domain? I ´thank you all in advance for reading, any help will be like a christmas´s gift. More or less... Greetings.. Can you give more detail...

external emails not delivered to mailboxes in DL when MESSAGE ID l
May be we don't receive a reply in different mailboxes which belong to a Distribution List. This happens when the original mail was sent using a Distribution List with the "from:" option. Then someone reply this mail and the Exchange log says the mail was delivered to mailbox-1, mailbox-2, mailbox-3, ... and this is not true, only was delivered to one of the mailboxes. This problem doesn't happend always but when happen the message ID is very long. For example this could be a message ID of an incoming message repling the original message sent with the DIstribution...

How to use string ID in MessageBoxW () ?
Hello! I have a string resource (can be in any lauguage) defined in the string table. Say the string ID is IDS_USER_GUIDE. Is there a way to display this string using MessageBoxW(...) sassily? I have tried this: CString csMsg (IDS_USER_GUIDE); and then pass it to the MessageBoxW like this: MessageBoxW (0, (LPCWSTR)(LPCTSTR)csMsg, "Test", 0); but it did not work. Thanks for you help. PS: I have to use MessageBoxW. I cannot define UNICODE because it introduces too many changes. Polaris "Polaris" <etpolaris@hotmail.com> wrote in message news:<OkGhQIwt...

Exchange IMF event ID 7514
Every hour we have an Event ID 7514 being generated after having installed IMF on our E2K3 server. IMF is not working and the error code in the event is 0x80004002. What is the cause of this error message? Do you have Exchange 2003 SP2 applied? Did you install IMF v1 after SP2? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Ian" <Ian@discussions.microsoft.com> wrote in message news:F52F0704-713B-489F-AAA6-E8AF5268EDF5@microsoft.com... > Every hour we have an Event ID 7514 being generated after having ...

registering my product wont work
OK - so Im trying not to freak out but it took me forever to save up for 2008 Home &amp; Student Edition. I ewnt to best buy, plunked down $165 after taxes, went home, opened it up, and found that there were three different product ID's pasted as labels on the back of the sleeve containing the install CD... Not knowing what to do i simply picked the one at the top and proceeded with installation, everything went along swimmingly - all my old crap was deleted and all my important settings migrated without a hitch however - when i went to register my software it keeps telling me that my ...

Mutliple Page Number sets
I'm setting up a book to go out to short run press. Using publisher because I get it through license for super cheap, so it didn't make sense to purchase an expensive publishing suite. But I want to do some things with the page numbers that I can't figure out to do. I want and intro and foreword to be in roman numerals, and then the main body of the text to have regular page numbers. What I really can't figure out though, is how to get the page numbers to start on "1" with the first page (currently starting on 11. I have Master Sheets applies to the differ...

OWA 2003 number of emails viewable
Hello, I was wondering if anyone knew where to set the number of viewable emails in OWA? By default it does not allow very many to be displayed. Thanks, Greg It's 25 by default. In OWA, click the Options button and then check the 'Messaging Options' section. There's an option called 'Number of items to display per page'. -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Greg" <anonymous@discussions.microsoft.com> wrote in message news:107801c47b40$76dd3b30$a401280a@phx.gbl... > Hello, > I...

CRM should allow auto numbering of the products
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=9c8e0ba6-f37a-4fc1-84fd-ff5b578e2c76&dg=microsoft.public.crm ...

Lowest number excluding zero
I have five columns for each vendor and I am trying to determine who has the lowest price out of the seven vendors. The data is not in an array. The critical data would be in i.e. cells: T20, Z20, AF20, AL20, AQ20 and AV20. I want to calculate the lowest price excluding zero out of those cells. I experimented by putting numbers into B4, C5, D6 and E7 I used Insert | Name | Define (in XL 2003) to define MYDATA to refer to =Sheet1!$B$4,Sheet1!$C$5,Sheet1!$D$6,Sheet1!$E$7 In XL 2007 use Formulas | Define names | Define Name Then In a cell I typed =MIN(mydata) and it returned the low...

Form of phone numbers in contact list
I am using Blackberry's Desktop Software to syncronize with my MS Outlook Contacts list. The onboard caller-id function of the phone that is supposed to correlate the incoming phone number to a name of a contact (should the number be in my contacts list) isn't working. According to help from Blackberry the problem is the "form" of the numbers as they're being loaded on the Blackberry from MS Outlook. Specifically, apparently the fact that the numbers are stored in the form of (###) ###-#### instead of something like ###-###-#### is keeping the phone from realizi...