Big Five Inventory in Excel?
I'm running a study and I'm having 30 people take the "Big Five Inventory." It's a questionnaire with 44 questions on it.
I'm going to have the participants listed down each row and their answers for each question listed in columns (horizontally). So, cell A1 would be answer 1 for person 1. B2 would be answer 2 for person 1. A2 would be answer 1 for person 2 and so on. This is how the BFI is scored:
To score the BFI, you’ll first need to reverse-score all negatively-keyed items:
Extraversion: 6, 21, 31
Agreeableness: 2, 12, 27, 37
Conscientiousness: 8, 18, 23, 43
Neuroticism: 9, 24, 34
Openness: 35, 41
To recode these items, you should subtract your score for all reverse-scored items from 6. For example, if you gave yourself a 5, compute 6 minus 5 and your recoded score is 1. That is, a score of 1 becomes 5, 2 becomes 4, 3 remains 3, 4 becomes 2, and 5 becomes 1.
Next, you will create scale scores by averaging the following items for each B5 domain (where R indicates using the reverse-scored item).
Extraversion: 1, 6R 11, 16, 21R, 26, 31R, 36
Agreeableness: 2R, 7, 12R, 17, 22, 27R, 32, 37R, 42
Conscientiousness: 3, 8R, 13, 18R, 23R, 28, 33, 38, 43R
Neuroticism: 4, 9R, 14, 19, 24R, 29, 34R, 39
Openness: 5, 10, 15, 20, 25, 30, 35R, 40, 41R, 44
Does anyone know what the function is in excel?
- ?Lv 410 years agoFavorite Answer
The formula you need is AVERAGE(), like this (assuming that Extraversion is in cell AS1):
=AVERAGE(A1, 6-F1, K1, P1, 6-U1, Z1, 6-AE1, AJ1)
You will use the same formula for each of the other four qualities, entering their formulas in cells AT1, AU1, AV1, and AW1. Any time you have an 'R' reverse-scored item, just enter the text '6-cell' instead of 'cell'. Be sure to separate each of the cells by a comma, as in the example above.
After you have all five formulas entered, you can copy/paste them to the other rows.
If you need to actually change the keyed value in the cells for the reverse-scored items first, rather than using the reverse-score in the average, you will have to do that using a macro. It's more complicated than the solution above, so post additional details if that's the case.
- voyagerLv 610 years ago
Here is an example..
A1:AR1 = question no 1,2,3,4,5,....44
A2:AR2 = category for each question (Extraversion, agreeableness, etc)
A3:AR3 = whether to use reverse-score or not (R or blank)
A4:AR12 -- i have the list of answers given by participants in A4:AR12 (1 to 5)
A16:AR22 -- new scores when the reverse-score is applied (using IF)
AS:16AW22 -- average score for each category (using SUMPRODUCT)
Let me know if you need more info or help. or email me, so i can send you the file i created for this one..