Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a Google Spreadsheet with the following data:

            A               B
   -----------------------------
1 | Number of responses | Value
2 |         1               1
3 |         3               2
4 |         0               3

Based on this data, I'd like to calculate the median response. The median in this case is obviously 2, since the responses are (1, 2, 2, 2). But how to do this calculation in Google Spreadsheets, preferably in an elegant way?

The best I've come up with is to write a custom function (Tools -> Script Editor):

/**
* Returns an array with the specified number of instances with the given
* instanceValue.
*/
function multipleInstances(numberOfInstances, instanceValue) {
  if (numberOfInstances == 0) {
    return;
  }

  var instances = [];

  for(var i = 0; i < numberOfInstances; i++) {
    instances.push(instanceValue);
  }

  return instances;
}

Then I've invoked the function to calculate the median:

=MEDIAN(multipleInstances(A2, B2), multipleInstances(A3, B3), multipleInstances(A4, B4))

This works, but is a bit cumbersome. Is there a more elegant way of saying "give me N instances of the value in this cell"?

share|improve this question
up vote 1 down vote accepted

The following little script will calculate the median at the same time.

Code

function myMedian(instances, values) {
  var ins = [], half;
  for(var i = 0, iLen = instances.length; i < iLen; i++) {
    for(var k = 0, kLen = instances[i]; k < kLen; k++) {
      ins.push(Number(values[i]));
    }
  }

  // reference: https://gist.github.com/caseyjustus/1166258
  half = Math.floor(ins.sort( function(a,b) {return a - b;} ).length / 2);

  return ins.length % 2 ? ins[half] : (ins[half - 1] + ins[half]) / 2;
}

Screenshot

enter image description here

Example

See example file I prepared: Median calculation

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.