Excel- make array from votes on a score

Question

I'm a whiz at Matlab, but apparently I can't figure out excel for my life today. I have a spreadsheet where I keep track of votes. So I record x number of votes for each score, i.e. on a scale of 1 to 5, 3 people voted 4, 2 people voted 3, and 1 person voted 1. I want to find the median of these votes, but I need to turn them into an array first, otherwise I'm just taking the median of the numbers of votes. I'm having trouble with getting arrays to work in this case. I need to build an array, with the above example, that looks like {4 4 4 3 3 1}, and then I can take the median of that (I assume I can just use the regular median function on an array?).

I realize the problem here is that I don't really know excel very well. So I guess I'm just asking for an answer, which is frowned upon when I can't show much work myself. But can someone give me a hint?

Answer 1

This one intrigued me, I'm sure there is a way to do this with an array formula but they have never been my strong point. For the time being here is a VBA solution:

Function MedianArray(rngScore As Range, rngCount As Range) As Double

Dim arrS() As Variant, arrC() As Variant, arrM() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim d As Double

arrS = rngScore
arrC = rngCount

d = WorksheetFunction.Sum(rngCount)

ReDim arrM(1 To d, 1 To 1)

k = 1

For i = 1 To UBound(arrS, 2)
    For j = 0 To arrC(1, i) - 1
        arrM(k, 1) = arrS(1, i)
        k = k + 1
    Next j
Next i

MedianArray = WorksheetFunction.Median(arrM())

End Function

Given you say you don't know much about VBA here's how you do it:

1. From Excel press Alt + F11 to open the VB Editor
2. In the VB Editor menus select Insert -> Module
3. Paste in the code above
4. In the cell where you need median value type =MedianArray(B1:F1,B2:F2), assuming your scores are in row 1 columns B through F and the counts are directly below.

Hope this helps.