Help with calculation of median

[sssss]

I want to calculate the median result of poll results where I know the number of votes each option received but the data isn't in the right format for the median function. For example, the Excel data looks like this:

Please Login or Register  to view this content.

This data shows that response 1 got 10 votes, response 2 got 15 votes and response 3 got 10 votes so the median is clearly 2. The question is how to get Excel to calculate this since the data isn't in the right format for the median function?

First post so please be kind

[NBVC]

Not sure what you mean...

The result you want is 2? Is that because median of 1,2,3 is 2? Not sure what you really want here? Please elaborate.

[sssss]

Sorry if it wasn't clear. In the example above the dataset for the median function is;

{1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3}

But the data I have in my worksheet is just the totals. eg 1 occurs 10 times, 2 occurs 15 times, 3 occurs 10 times.

The thing I'm trying to figure out is how to convert these totals into the right format for the median function.

[Dav]

If the responses are votes, I am not sure you can calculate a median. Are reponses 1, 2, 3 ordinal? If no the answer is no to a median.

Regards

Dav

[NBVC]

Not sure about a single formula solution at the moment... but here is a way with some helper rows...

Assuming your data is in A1:C2

then in A3:

=IF(ROWS(A$3:$A3)>A$2,"",A$1) copied down as far as the most number of votes you would receive.

Then copy formulas to columns B and C

Then use: =MEDIAN(A3:C33)

where I have assumed up to 30 possible number of votes (ie. formula was dragged down to Row 33)

Maybe also possible with VBA.. but don't know how.

[DonkeyOte]

If we assume your values are in B1:D2 then perhaps:

Please Login or Register  to view this content.

would work for you ?

[sssss]

Thanks NBVC - that helper rows solution does convert the totals into the raw values but I think in reality there could be thousands of votes so I was hoping there would be a way to do it all within a formula

[NBVC]

Test out DonkeyOte's suggestion.

[sssss]

@DonkeyOte - your solution works perfectly! Many thanks for the prompt help