Calculating the Median in Numbers from a list of people who voted in a certain way.

Question

Calculating the Median in Numbers from a list of people who voted in a certain way.

I have a number of values, each value represents the number of people who voted in a certain way. I need to find the median among the voted values.

In Numbers the function Median takes a list of values. For example we could have 8 people voting (2,1,2,2,2,1,0,1). If I had the votes expressed in this way I could apply Median (2,1,2,2,2,1,0,1) = 1.5 . But I receive them as:

Header 1	Header 2	Header 3	Header 4
Vote expressed	0	1	2
Number of people who voted in this way:	1	3	4

And I really don't know how to calculate the median in those conditions. Let me add that I have this problem several hundreds of times, for several hundreds collections of votes. So doing it by hand is really not an option.

Any help would be appreciated.

Pietro

iWorks Numbers-OTHER

Posted on Oct 19, 2013 12:28 AM

Reply

Answer 1

Barry

Level 9

60,023 points

Oct 19, 2013 1:30 PM in response to pietrosperoni

Hi Pietro,

Here's a method that may work for you. It uses an auxiliary row (which may be hidden) to determine the median (and values above the median), followed by a formula that selects the median from the list created by the first formula:

Formulas:

B3 and filled right: =IF(SUMIF(2:2,"<="&B2,3:3)=SUM(3:3)/2,AVERAGE(B2:C2),IF(SUMIF(2:2,"<="&B2,3:3)> SUM(3:3)/2,B2,""))

B4: =MIN(B4:D4)

The first IF condition: SUMIF(2:2,"<="&B2,3:3)=SUM(3:3)/2

handles the situation where the numbers of responseses below the median is exactly half the total number of rsponses. In this case, the median is the mean of the greatest value below the median and the least value above the median (in your sample, 1.5).

If the condition is not met, the formula goes to the second IF condition: SUMIF(2:2,"<="&B2,3:3)>SUM(3:3)/2

which tests if more than half the responses are less than or equal to the response tallied in 'this column'. if met, it paces the value in row 2 into its cell as a possible median. The actual median is the value placed when one of these two conditions in first met (ie. the minimum value place in this row). If neither condition is met, the formula places a numm string ( "" ) in its cell. This value is ignored by MIN().

In B4, MIN(3:3)

Returns the MEDIAN value of the votes, the minimum numerical value in row 3.

Regards,

Barry

Reply

Answer 2

Oct 19, 2013 2:55 PM in response to Barry

Hi Barry, thank you very much. I could not put a row below because I have about 100 rows and each is a different voting event. But I can put them in a separate table so that will be ok.

What I am more worried is that I gave this example with 3 values (0,1,2), but in reality I have five (-2,-1,0,1,2). Not sure how to generalise your solution. You seem to use the fact that you know how many votes has been cast, and thus if half of them are in the first column or the last one. With 5 columns it might not be that easy.

Cheers,

Pietro

Reply

Answer 3

Barry

Level 9

60,023 points

Oct 19, 2013 3:39 PM in response to pietrosperoni

Hi Pietro,

Your example shows two rows for each event, the first showing the choices available, the second the number of votes for each of the choices.

The number of votes cast is the sum of the second of these rows.

There are three possible results from the comparison of these two counts:

The count of votes in "this column" and those to the left of it is less than half the total count, and the formula returns a null string (which appears 'blank') to its cell in "this column".
The count of votes in "this column" and those to the left of it is equal to half the total count, and the formula returns the mean of the vote value in "this column" and the vote value in the "next column" to its cell in "this column".
The count of votes in "this column" and those to the left of it is greater than half the total count, and the formula returns the vote value in "this column" to its cell in "this column".

In each case, the actual MEDIAN value is the smallest (non-string) value in the row containing the formula.

Here's the same example with two more choices, revised vote values, and a revised count:

No revision to the formulas was necessary—they ust need to be filled right into the two new columns.

Regards,

Barry

Reply

Answer 4

Oct 20, 2013 1:35 AM in response to Barry

thanks!

Reply