Tagging a row by odd / even number input

Ask the community Formulas
#1

Hi, I have a base where we key in the ID numbers of each person in a column - Males have odd numbers and females have even numbers.

Would like to create a column that automatically tags them as either Male or female based on this data but I cant seem to figure our what formula to use. Could someone help me? It would be of great help.

Examples:

XXXXXX-XX-5557 = Male
XXXXXX-XX-5556 = Female.

#2

Hi Santhanasamy, You can achieve this by comparing the last digit against a Switch() statement, like this:

SWITCH( RIGHT(ID,1), 
"1","Male","2","Female", 
"3","Male","4","Female", 
"5","Male","6","Female", 
"7","Male","8","Female", 
"9","Male","0","Female" 
         )
2 Likes
#3

Yes, that would work fine!

Another way to do this would be to use the MOD() function like this:

IF(MOD(RIGHT(ID,1),2)=0,"Female","Male")

And if the ID field is a number field, you’ll need to convert it to text first in order to make it work with the RIGHT() function like this:

IF(MOD(RIGHT(ID&"",1),2)=0,"Female","Male")

1 Like
#4

Hi, thank you this worked!

#5

Hi, I tried the 1st formula but there was a problem, if the ID cell was blank, it would also tag the record.

#6

Then you would need to wrap that formula in another IF statement to check if the ID cell is blank:

If ID is a text field:
IF(ID=BLANK(),"",IF(MOD(RIGHT(ID,1),2)=0,"Female","Male"))

If ID is a number field:
IF(ID=BLANK(),"",IF(MOD(RIGHT(ID&"",1),2)=0,"Female","Male"))

#7

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.