I'm currently using Microsoft Excel 2007 to analyze data from a bunch of surveys. I've already coded all the answers on paper and have begun to put some data in. However, before I get too far I want to make I do this right. I need to know how to 'code' the numeric data I've entered so that when analyzed it is replaced with text.

 

For instance....

 

Question #2 - Please Check your age category

 

<25 (1)

26-45 (2)

46-64 (3)

Over 64 (4)

 

How do I code the spread sheet so 1 = <25, 2 = 26-45, 3 = 46-64, and 4 = Over 64?

So where are they "checking" the data? On the spreadsheet itself? Or on a VBA userform?

You can string together IF Statements

 

 

are you wanting it so that they have a dropdown to choose their age group and it displays a number 1-4 in another cell?

 

if you want, we can discuss this via email

For example. If I highlight cell C1 and go to Data|Validation and change the Allow: value to "List" by clicking on the down arrow and typing "<25,26-45, 46-64, Over 64" into the box labeled Source (without quote marks). Then clicked ok. This will make it so when I click on C1, a drop down arrow appears allowing me to choose one of those options.

 

Then in another cell (in my test file I used C3) I used the following formula,

=IF(ISBLANK(C1),"",IF(C1="<25",1,IF(C1="26-45",2,IF(C1="46-64",3,4))))

 

What this does is first, checks to see if it's blank, if it is, it leaves C3 blank. Then it checks to see if the value in C1 is <25, if it is, it puts a 1, if it's not, it checks for the next option, and the next, etc until you have your 4 options covered.

 

Is that what you're looking to do?

I'm not sure of a way without VBA code where you can type a value in the cell and have it display another value in that same cell. With Excel there isnt a Yes and No value, there's True and False, with 1 being True and 0 being false

If I ever doubt my career choice (graphic design aka we do as little as possible with Excel), all I have to do is check this thread. Good lord, my right-brained mind is about to explode reading all this formula stuff!!! :-o

I would build a database in MS Access; you can add dropdown fields or the value of a field based on 1 = x, 2 = x, etc. Setup a form for the data entry part.

How to Automatically Add Predefined Values to a Microsoft Access Table Field:

 

http://www.ehow.com/how_12376_automatically-add-predefined.html

If I ever doubt my career choice (graphic design aka we do as little as possible with Excel), all I have to do is check this thread. Good lord, my right-brained mind is about to explode reading all this formula stuff!!! :-o

 

this is what I do all day long, darlin..

Ack! Better you than me! :-)

The sh!t I learn on UrbanOhio...

Would VLOOKUP Do what you want?

 

 

That function returns another value from an array that matches the row corresponding to an index value

 

(See link for screen shots and examples)

 

---------------------------------------------------------

http://www.techonthenet.com/excel/formulas/vlookup.php

 

Excel: VLookup Function

 

In Excel, the VLookup function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.

 

The syntax for the VLookup function is:

 

    VLookup( value, table_array, index_number, not_exact_match )

 

value is the value to search for in the first column of the table_array.

 

table_array is two or more columns of data that is sorted in ascending order.

 

index_number is the column number in table_array from which the matching value must be returned. The first column is 1.

 

not_exact_match determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLookup function will look for the next largest value that is less than value.

 

Note:

 

If index_number is less than 1, the VLookup function will return #VALUE!.

 

If index_number is greater than the number of columns in table_array, the VLookup function will return #REF!.

 

If you enter FALSE for the not_exact_match parameter and no exact match is found, then the VLookup function will return #N/A.

Would VLOOKUP Do what you want?

 

 

That function returns another value from an array that matches the row corresponding to an index value

 

(See link for screen shots and examples)

 

---------------------------------------------------------

http://www.techonthenet.com/excel/formulas/vlookup.php

 

Excel: VLookup Function

 

In Excel, the VLookup function searches for value in the left-most column of table_array and returns the value in the same row based on the index_number.

 

The syntax for the VLookup function is:

 

    VLookup( value, table_array, index_number, not_exact_match )

 

value is the value to search for in the first column of the table_array.

 

table_array is two or more columns of data that is sorted in ascending order.

 

index_number is the column number in table_array from which the matching value must be returned. The first column is 1.

 

not_exact_match determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then the VLookup function will look for the next largest value that is less than value.

 

Note:

 

If index_number is less than 1, the VLookup function will return #VALUE!.

 

If index_number is greater than the number of columns in table_array, the VLookup function will return #REF!.

 

If you enter FALSE for the not_exact_match parameter and no exact match is found, then the VLookup function will return #N/A.

 

It still wouldnt allow him to type a 1 in a cell, press enter and have it display "yes", not in the same cell. He's going to need either code or use Access and a form as suggested

If you need more info regarding the vlookup function:

vlookup

There is no possible way to have the value replaced in the same cell without using VBA.  If you want the answers to be shown next to the numbers, it would be easy using either vlookup or hard-coded (and messy) nested IF statements similar to what SmittenKitten showed (although it'd be in reverse as he was going from text to number).  You could have the columns next to each other and have the headings span both columns.

 

I think you are bordering on trying to use Excel for something it was not made to do (which I find to be all to common in the business world).  Access (or any other database system) was tailor made to handle tasks like this.  I wish Access would become more common with people instead of Excel because I think it forces you to think about how to organize your data more (by defining a relational database structure).  Then you could write any number of reports against the data that would display it however you want, without mixing the ideas of storing data and viewing data.