Excel problem

cubelover

Hey everyone,

I have a basic understanding of excel and I'm trying to create an automated form with three columns. The 'A' column is a survey-type question, the 'B' column is a drop down list of potential responses (linked from another worksheet) and in the 'C' column I would like an automated numeric score based on the response selected in 'B'.

Example: Are you male or female? Drop down gives you the choice of one or the other. Final column gives an automated score of 1 for 'female', 0 for 'male.'

Any ideas how I code for the automated scoring? I'm at a total loss (I'm not even sure what I would google for this) and would appreciate any help!

Thanks!

Daenris

Well I don't do much form stuff like this in Excel, but check out the vlookup function. You can have a table on another sheet looking something like this:

column A   column B
Male        0
Female      1

and use a vlookup function to give the value from column B that lines up with the column A result that matches your drop down response.

FunkyWaltDogg

You might try the IF function. Say your response is in B6, put this in C6:

=IF(B6="Male",0,IF(B6="Female",1,7))

It returns a 0 for Male, 1 for Female, 7 for anything else.

Drez

Yes, that's pretty much it. Use VLOOKUP or use an IF statement.

I recommend the VLOOKUP method as you can add and delete things on the fly in a much easier and more intelligble manner. For something as simple as "male" vs. "female" it won't matter much because you can't really have any other choice but if you start adding new survey questions and values you can just add to your scoring table, more or less.

cubelover

FunkyWaltDogg wrote: »

You might try the IF function. Say your response is in B6, put this in C6:

=IF(B6="Male",0,IF(B6="Female",1,7))

It returns a 0 for Male, 1 for Female, 7 for anything else.

That works great! Thanks for the quick response!

Daenris

Keep in mind that if you have things much more complex than two choices, your if statements will get pretty long and ugly quickly, whereas a vlookup won't look any different (besides referencing a larger table).