Median in Excel will not work

[jkonline2]

I have generated the following totals from 15 questionnaires where people could choose the options 1 to 5


Option: 1 2 3 4 5
Total: 5 5 1 3 1

*This is example data

However I wish to find the median number but if I use the Excel calculation for median it gives me the median for the 5,5,1,3,1 rather than 11111,22222,3,444,5. What ever I try I can not work out the formula.

 

[substitute]

Your problem interested me so I spent some time working on it. There doesn't seem to be an easy solution, because median is about the "middle value" and so cannot be calculated from weights, etc. The best I could come up with without writing a macro was a set of hideous formulas.

Assuming your values are in cells A1 to E5 and counts are in A2 to E2 add the following formulas to the cells shown:

F2: =SUM(A2:E2)

A3: =IF(A$2=0,0,IF(ISEVEN($F$2),IF($A$2>$F$2/2,1,IF($A$2=($F$2-1)/2,0.5,0)),IF($A$2>=($F$2+1)/2,1,0)))

B3 and copy to C3:E3: =IF(B$2=0,0,IF(ISEVEN($F$2),IF(SUM($A$3:A$3)=0.5,0.5,IF(SUM($A$3:A$3)=1,0,IF(SUM($A$2:B$2)>$F$2/2,1,IF(SUM($A$2:B$2)=$F$2/2,0.5,0)))),IF(SUM($A$2:B$2)<($F$2+1)/2,0,IF(SUM($A$3:A$3)=1,0,1))))

F3: =SUMPRODUCT($A$3:$E$3,$A$1:$E$1)

The result is in F3

That works for my test data and should work for any data. If you have more then 5 values you need to stretch all the &quot;:$E&quot; references to the right and move the &quot;F&quot; cells.

The conditional (IF) formulas test for even number of samples (median is midway between two samples) or odd number of samples (median is on a sample) and deal with all the exceptions (no samples, median between two samples of same value etc).

The A3 formula is different to B3:E3 because there are no samples to the left.

You could incorporate the sum in F2 into the formulas in A3 to E3 if you wanted to get rid of F2. The row 3 formulas have to go below the data but F3 can go anywhere

Does this help?

 

[jkonline2]

All boxes in row 3 are giving me #NAME? which I understand as an invalid refrence. Now looking at the formula and double clicking it so it highlights the dependant cells it looks fine?