3

I want to create an array/ a list of cells (i don't know the exactly terminology), in rows or columns form, covering the range between 2 numbers

Fox example, I have 2 cells, A1 = 3; B1 = 10

I want to generate in a dynamic way, an array of cells that cover the range between A1 and B1:

C1 = 3
C2 = 4
C3 = 5
...
C8 = 10

1

I would use a script for this. Something like this:

function generateArray() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dest = sheet.getActiveRange();
  var destRow = dest.getRow();
  var destCol = dest.getColumn();
  var startValue = sheet.getRange(1, 1).getValue(); //Start value in cell A1
  var endValue = sheet.getRange(1, 2).getValue();   //End value in cell B1

  for (i = startValue; i <= endValue; i++){
    sheet.getRange(destRow, destCol).setValue(i);
    destRow++;
  }
}

Select the cell where you want the first number to appear and run the script.

If you really want to use a function you could use this:

=IF(AND(C1+1<=$B$1,C1<>""),C1+1,"")

Set the first cell (C1 in this case) to =A1 and paste the formula above in C2 and drag the corner down so that it's copied down.

The downside with the formula, especially if you have a big range, is that you have to drag it to cover the same amount of cells that is in the range. If you use the script ou just have to select the first cell and run it. An advantage with the formula, though, is that the cells update automatically. (You could modify the script to run automatically when you edit the range cells, though, if that is something you want.)

1

A formula that could be placed in any column (other than A or B) in Row1 and that does not require C1 to be seeded with the A1 value:

=if(row()<B$1-A$1+2,A$1+row()-1,"")  

However, as with @Punchlinern's, needs to be copied down far enough to occupy at least one more row than the difference between B1 and A1.

1

This can also be achieved using ArrayFormula:

=ArrayFormula(row(offset(A$1,0,0,max-min+1))+min-1)

Similar to @pnuts's answer, it manipulates the row() function to give the range of values you want.
Note that the A$1 can refer to any column, its content doesn't matter as long as it's in the first row. The offset function then defines the range of values we need.

In general, you can treat the argument of the row function as the (integer) domain of x and use it to calculate any mathematical function. E.g. first 10 even numbers (y = 2x) can be given by ArrayFormula(2*row(1:10)).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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