recode in excel This is a topic that many people are looking for. saibabalinks.org is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, saibabalinks.org would like to introduce to you Recoding Variables in Excel – YouTube. Following along are instructions in the video below:
“This is dr. Grande welcome to my video on recoding variables in excel in counseling counseling research. It s fairly common to use microsoft excel to manipulate and arrange data then load that data into spss. So that statistical analyses can be performed.
There is however some overlap between excel and spss and recoding variables is an example of one of those areas of overlap so in spss. There is a feature and recode in two different variables. And i have a separate video that covers that but i m going to perform the same function in excel using the fictitious data. I have here in this worksheet.
So i have here three variables. I have one independent variable two levels control and treatment. And i have a pretest variable and a post test variable. So let s say we want to recode the pretest variable from the values that are currently in this variable into new values that are based on these values.
So i ll show you a couple examples that but the first thing we need to do is determine the range present in the pretest. More specifically the minimum and the maximum value represented in the pretest variable. So that s fairly easy to do in excel first. I ll run the minimum in this case will just be the entire column and we can see that s 40 and the maximum again the entire column is 60.
So we know the minimum value in the pretest variable is 40 in the maximum is 60. So i m going to move here to another worksheet that i named data and i m in column a. I m just going to enter 40 and then 41 and cot in cell a2. Then i m going to autofill all the way down to 60 you can see i have the entire range of possibilities for pretest contained in column.
A one this data worksheet so i m going to show you two possible ways to invert the state of recode. This data in terms of what the output might look like so really it s the same method. It s just two different possible types of output. One would be scale right so the easy example here would just be 1 2.
And then all the way down to 21 and the other will be ordinal. Which i ll show you in a few minutes. So let s just recode 4260 into 1. Through.
21. Let s start with that so that s scale and spss. So this would be referred to as scale to another scale variable here in column b. So we ll go to the first worksheet that has the data and i m going to go ahead and put a grid or gridlines around the entire area just to keep it consistent and this will be a new pretest variable.
So we ll just call this pretest. Let s call this recode and center. It and i ll fill color. It s so i make it orange just make it consistent with the rest of the worksheet.
So then i m going to enter a function into this cell. That s going to look up this value on this table and return the corresponding value. So let s go look up the value in column. A and return the corresponding value from column b.
Which will accomplish the recode function that we want to achieve. So this the function will be entered into this cell. You ll be able to copy all the way down through the entire all the rows and it ll work correctly. But you have to setup in a specific way to be able to copy it all the way down so i ll show you how we do that so we re going to be using the index and match functions together.
If you want to learn more about this combination. I have another video on that as well so first we ll start with index and you can see the first criteria. The first argument that is looking for here is array and this array is going to actually be column b on the data on the data. Worksheet.
Specifically b12. B21 and i clicked f4 there at the end to make this absolute. So that puts in the dollar signs to make this an absolute reference put a comma and you can see the next argument. It s looking for is the row number so the row number you re going to want to match the pretest data.
So we re going to use the match function nested in the index function and it s going to ask for lookup value so going back to the pretest post test worksheet. The lookup value is going to be b. 2. In this case.
Then it s asking for the array and we re going to go back to data. And the array will be the populated cells in column. A which would be a 1 through a 21 again. I m going to click f4 here to make this absolute just want to note that when selecting the lookup value.
Which was b to do not make that an absolute reference. But the lookup array. You do want to be absolute in this case. We re going to end with exact match just a 0 and you can see it s recoded 43 into.
4. If you look here 43. Does in fact match up with 4. So it did what we wanted to do and since it s set up with the absolute references in the correct places.
And it has the relative reference here for b2. We can autofill this all the way to the end and they ll return the correct values. So this would be a scale to scale so scale level measurement recoding. It to a scale variable.
Now once we have this function in place. As we do now. It becomes very easy to change how we recode this pretest variable. So say we wanted to make this it s the combi instead of 1 through 21.
We want to be ordinal. Let s say a low medium and high so we ll say that 40 is low on to that value and we can autofill that let s say 246. So if participants scores between 40 and 46. Their ordinal value associated with those scores is low and in a similar fashion.
We can have a medium level. And we can make that from let s say 47 through 55. And then 456 through 60 will have hi and so now this is configured differently. This is going from scale to ordinal.
We go back here. We can see i had to change nothing on this worksheet because the function was already in place. And it s correctly recoded the pretest variable into low medium and high depending on the specified value in the particular row. So this accomplishes the same end result as can be done with spss using the recode in two different variables.
Feature. And it has some advantages and disadvantages in comparison with spss. The disadvantage would be it s a little harder to set up it takes so longer because you have to you have to build this table. And you have to insert the function its advantage is that once it s configured.
The advantage for excel is once it s configured you can go in to this table to these two columns and very easily change the variable recode variable you can change the values quite easily and not have to change anything else on the worksheet that contains the data and the recode will take place before you even click over into the the worksheet. So it s it s convenient to change this data change. The recode and then quickly be able to see how the recode looks and then maybe go back and change something else and again come back and view it so it you don t have to keep executing. A command to see the recode and of course you can take this data and import this directly into spss and i have another video that covers how to do that i hope you found this video on recoding variables in excel to be helpful as always if you have any questions or concerns feel free to contact me.
And i ll be happy to assist you ” ..
Thank you for watching all the articles on the topic Recoding Variables in Excel – YouTube. All shares of saibabalinks.org are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
This video demonstrates recoding variables using Microsoft Excel. It compares recoding variables with Excel to the Recode into Different Variables feature in SPSS.
Microsoft Excel, recode variables, Recode into Different Variables, SPSS, index, match, scale, ordinal, nominal, levels of measurement, min, max, Software (I…