Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reverse code data (1=5, 2=4, etc.)?
I have data sets in only certain columns of my spread sheet that need to be
reverse coded, i.e. 1=5, 2=4, 3=3, 4=2, 5=1 . How do I get these new values in Excel? |
#2
|
|||
|
|||
Answer: How to reverse code data (1=5, 2=4, etc.)?
This will replace the original values with their reverse-coded counterparts in the selected column(s). You can then delete the new column that you created in step 2.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reverse code data (1=5, 2=4, etc.)?
If the data is sorted (or can be) by the column with those numbers, then:
1. Sort the records by the column with numbers that need changing. This will get all of the 1's together, and all the 2's together, etc. 2. Then highlight the 1's. 3. Press 5 then CTRL+ENTER. This places a 5 in all of the cells that had a 1. 4. Repeat for the other numbers that need to be swapped. 5. Resort if needed after all the changes are made. "jhk150" wrote: I have data sets in only certain columns of my spread sheet that need to be reverse coded, i.e. 1=5, 2=4, 3=3, 4=2, 5=1 . How do I get these new values in Excel? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reverse code data (1=5, 2=4, etc.)?
I am not able to sort the values because the data would not correspond with
the row with the respondent's ID number. Is there anything else I can do? "BobT" wrote: If the data is sorted (or can be) by the column with those numbers, then: 1. Sort the records by the column with numbers that need changing. This will get all of the 1's together, and all the 2's together, etc. 2. Then highlight the 1's. 3. Press 5 then CTRL+ENTER. This places a 5 in all of the cells that had a 1. 4. Repeat for the other numbers that need to be swapped. 5. Resort if needed after all the changes are made. "jhk150" wrote: I have data sets in only certain columns of my spread sheet that need to be reverse coded, i.e. 1=5, 2=4, 3=3, 4=2, 5=1 . How do I get these new values in Excel? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reverse code data (1=5, 2=4, etc.)?
Can you select the values you want to change? If yes then do the following;
1. Enter -6 in any unused cell Edit-Copy Select the cells you want to change Edit-Paste Special-Add 2. Enter -1 in any unused cell Edit-Copy Select the cells you want to change Edit-Paste Special-Multiply Alternatively enter this next to the column which has the values you want to change; (assuming Col A, enter this in B1 and copy down) =IF(AND(A10,A1<6),(6-A1),A1) You can then Copy the Col, do Edit-Paste Special-Values and then delete 0s. "jhk150" wrote: I am not able to sort the values because the data would not correspond with the row with the respondent's ID number. Is there anything else I can do? "BobT" wrote: If the data is sorted (or can be) by the column with those numbers, then: 1. Sort the records by the column with numbers that need changing. This will get all of the 1's together, and all the 2's together, etc. 2. Then highlight the 1's. 3. Press 5 then CTRL+ENTER. This places a 5 in all of the cells that had a 1. 4. Repeat for the other numbers that need to be swapped. 5. Resort if needed after all the changes are made. "jhk150" wrote: I have data sets in only certain columns of my spread sheet that need to be reverse coded, i.e. 1=5, 2=4, 3=3, 4=2, 5=1 . How do I get these new values in Excel? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reverse code data (1=5, 2=4, etc.)?
jhk150 -
If you have one row for each respondent, then you can use Data | Sort with the desired column as the sort key. Each row will remain intact. Make a backup copy of the worksheet first. - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "jhk150" wrote in message ... I am not able to sort the values because the data would not correspond with the row with the respondent's ID number. Is there anything else I can do? "BobT" wrote: If the data is sorted (or can be) by the column with those numbers, then: 1. Sort the records by the column with numbers that need changing. This will get all of the 1's together, and all the 2's together, etc. 2. Then highlight the 1's. 3. Press 5 then CTRL+ENTER. This places a 5 in all of the cells that had a 1. 4. Repeat for the other numbers that need to be swapped. 5. Resort if needed after all the changes are made. "jhk150" wrote: I have data sets in only certain columns of my spread sheet that need to be reverse coded, i.e. 1=5, 2=4, 3=3, 4=2, 5=1 . How do I get these new values in Excel? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reverse code data (1=5, 2=4, etc.)?
Your data being in column A
=MAX(A:A)-A1+1 formula in a helper column reverses column A values. Regards, Stefi €žjhk150€ť ezt Ă*rta: I have data sets in only certain columns of my spread sheet that need to be reverse coded, i.e. 1=5, 2=4, 3=3, 4=2, 5=1 . How do I get these new values in Excel? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reverse Order of Data Set | Excel Worksheet Functions | |||
Can I reverse the order of data in a set of cells? | Excel Discussion (Misc queries) | |||
reverse data | Excel Discussion (Misc queries) | |||
Reverse Data from bottom to top | Excel Discussion (Misc queries) | |||
DATA VALIDATION IN REVERSE | Excel Discussion (Misc queries) |