Nov 29, 2020 11:33 PM
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.
Solved! Go to Solution.
Dec 04, 2020 08:53 PM
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"
)
Dec 04, 2020 08:53 PM
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"
)
Dec 04, 2020 09:56 PM
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")
Dec 07, 2020 06:19 PM
Hi, thank you this worked!
Dec 07, 2020 06:21 PM
Hi, I tried the 1st formula but there was a problem, if the ID cell was blank, it would also tag the record.
Dec 07, 2020 08:34 PM
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"))