Google Sheets – Creating Multiple Instances of a Cell Value

google sheetsgoogle-apps-script

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"?

Best Answer

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

Example

See example file I prepared: Median calculation

Short answer

Instead of using a custom function use a built-in function when possible or use a function triggered by an event.

Explanation

As the OP mentioned, Google Sheets has a built-in function to return a random numbers between 0 and 1: RAND.

In cases where it's required to do some complex calculations that are not possible or efficient with built-in functions, one alternative is to use Google Apps Script. It's worth to bear in mind that custom functions are recalculated only when one of their arguments changes, by the other side their arguments should be deterministic, so using functions like NOW(), TODAY(), RAND() and RANDBETWEEN() will return an error even if they are nested in other functions spreadsheet functions or included indirectly through references.

Google Apps Scripts has two kind of events, simple and installable. See https://developers.google.com/apps-script/guides/triggers/ for details.

Example

The following code will update the specified single cell reference every time time that an user edits the spreadsheet with a random number generated by a the Math.random().

var reference = 'A1';

function onEdit(e) {
  SpreadsheetApp
    .getActiveSheet()
    .getRange(reference)
    .setValue(Math.random());
}

References

Google Sheets – Array-Safe Clamp Value

You can achieve the effect of CLAMP(value, min, max) with

=IFERROR(max-SQRT(max-IFERROR(SQRT(value-min)^2+min,min))^2,max)

which is compatible with ARRAYFORMULA and uses value only once. Explanation:

SQRT(value-min)^2+min returns value if value>=min, and gives an error otherwise.
IFERROR returns the first argument, unless an error occurred in it, which case it returns the second
Thus, IFERROR(SQRT(value-min)^2+min,min) clamps the value from below by min.
The other steps do a similar thing with max.

The above formula can be simplified, reducing the references to max and min from three to two of each:

=max-IFERROR(SQRT(max-min-IFERROR(SQRT(value-min)^2,0))^2,0))

Best Answer

Code

Screenshot

Example

Related Solutions

Google-sheets – Google Spreadsheet generate random numbers with Math.random()

Short answer

Explanation

Example

References

Google Sheets – Array-Safe Clamp Value

Related Topic