Converting drop-down boxes into digits

[ignatiusvienna]

Hey folks,
Having an issue with our Excel data collection and hoping for help.

I run data collection for national survey we are conducting for our organization. For years we have used an Excel spreadsheet to catalog the demographic information of our members and have used drop down boxes for ease of data entry. We now have access to SPSS and hope to use this for more in-depth data analysis. Unfortunately, SPSS requires digit values for categories and the drop-down information can’t simply be copy and pasted.

What I’m looking for is a formula that can convert the drop-down boxes in Excel into numbers, either replacing the drop-down information with digits, or placing corresponding digits in a new column (so they may then be copy and pasted into SPSS).

So, if for example we had a drop down for gender, in one column I have a drop-down list of
Male
Female
Male
Male
Male
(so on)

I want either that columns to convert to this, or have a sister column list:
1
2
1
1
1


Can this be done? Thank you in advance

 

[CalcSux78]

A helper column is the easiest way:

Excel 2010

	C	D
Gender	GenderCode
Male
Female
Male
Male
Male
Female

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=IF(C2="Male",1,IF(C2="Female",2,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ignatiusvienna]

Excellent, thank you.

That seems to work well for two categories, but I get an error when there's more than 2 categories. How could I modify that formula to work for multiple options (ie: Male/Female/Transgender)

 

[CalcSux78]

So it's a nested IF statement. Within the formula, replace the double quotes with additional levels.

Example:
<!--StartFragment-->=IF(C2="Male",1,IF(C2="Female",2,IF(C2="Transgender",3,"")))<!--EndFragment-->

 

[Rick Rothstein]

So it's a nested IF statement. Within the formula, replace the double quotes with additional levels.

Example:
<!--StartFragment-->=IF(C2="Male",1,IF(C2="Female",2,IF(C2="Transgender",3,"")))<!--EndFragment-->

If the OP is willing to list the possible values in cell C2 in alphabetical order (remembering to coordinate the conversion numbers to that order when used for longer lists), this formula could be used...

=LOOKUP(C2,{"Female","Male","Transgender"},{2,1,3})

 

[ignatiusvienna]

Thank you for all your help! It's working great.
Last question, can this same kind of formula be used for converting a date into a digit. ie, we date our entries (9/15/2017 for example) and have categories of(early September, later September, Early October....).

Is there a way to have a helper column with this formula that takes the date and spits out a corresponding digit? (ie, 1 for early Sept, 2 Late Sept....)

Thank you a million

 

[Rick Rothstein]

Thank you for all your help! It's working great.
Last question, can this same kind of formula be used for converting a date into a digit. ie, we date our entries (9/15/2017 for example) and have categories of(early September, later September, Early October....).

Is there a way to have a helper column with this formula that takes the date and spits out a corresponding digit? (ie, 1 for early Sept, 2 Late Sept....)

For two choices, you could use this for your spelled out categories...

=IF(C2< C2-DAY(C2)+DAY(EOMONTH(C2,0))/2,"Early ","Late ")&TEXT(C2,"mmmm")

and this for you numbers...

=IF(C2< C2-DAY(C2)+DAY(EOMONTH(C2,0))/2,1,2)

 

[ignatiusvienna]

Again, thank you so much!!!