Search code examples
excelmedian

Research data analysis in Excel: Median of x times column value

I need to analyze empirical research data.

Question      Fully agree (1)   Agree (2)    Neither(3)      Disagree (4) 

Q1                 7               3             2             5   
Q2                 10              7             0             0  
Q3                 4               3             3             7  
Q4                 15              0             2             0

So for Question Q1, 7 people fully agree, 4 agree, etc.

I have to calculate the median for all questions. The result should look like this:

Question      Fully agree (1)   Agree (2)    Neither(3)      Disagree (4)    Median

Q1                 7               3             2             5               2

Q2                 10               7             0             0               1

For Question Q1, 7 people answered with "fully agree", 3 with "agree", 2 with "Neither" and 5 with "Disagree". I want to calculate the Median of the Answers for Question Q1: "Fully agree" is equivalent to the number 1, "Agee" to 2, Neither to 3 and Disagree to 4. So all answers for Q1 would be: 1,1,1,1,1,1,1,2,2,2,3,3,3,4,4,4,4,4. The Median is: 2

For Q2 it is: 1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2. The median is 1

Usually I don't work with Excel, so all functions are new to me. I have now tried different solutions for several hours: e.g. VLOOKUP (Copy value N times in Excel)

Unfortunately I was not successful so far; maybe there is a very simple solution to calculate the median :). Hopefully a solution that is not based on VPA, as I have even less experience here. Thank you very much for your help

Solution

  • Here's a fun way of doing it with a pretty simple formula:

    =MID(REPT(1,B3)&REPT(2,C3)&REPT(3,D3)&REPT(4,E3),SUM(B3:E3)/2,1)

    enter image description here

    See the Explanation column,, that shows how I'm building a string consisting of all the values with REPT(1,B3)&REPT(2,C3)&REPT(3,D3)&REPT(4,E3). All I have to do then is extract the middle vallue, which is what the MID(Explanation,Number of entries / 2, 1)

    If you have an even number of answers, and want to split the difference should say the middle point be between the values of 2 and 3, you can use this array-entered formula: =AVERAGE(VALUE(MID(REPT(1,B3)&REPT(2,C3)&REPT(3,D3)&REPT(4,E3),SUM(B3:E3)/2+ISEVEN(SUM(B3:E3))*{0,1},1)))