How can I create an array of sequential values through the input of the minimum and maximum values?-CodePudding

Let's say I want a logical sequence between 0 and 100 that has 1 number between them, so I set 0 in B1, 100 in B3 and add the formula in B2:

If I want with 9 numbers between them, I just change the cell where the 100 is:

Now I want to reproduce this using Google Apps Script so I don't have a circular dependency on my spreadsheet.

[[0],
[...],
[...],
[...],
[100]]

So far, I've tried this:

function between() {
  const min = 0;
  const max = 100;
  const total = 11;
  const seq = (min   max) / total;
  const lst = [[min]];
  let i = 1;
  while (i <= total-2) {
    lst.push([(seq*i).toFixed(2)])
    i  
  }
  lst.push([max])
  SpreadsheetApp.getActive().getSheetByName('data').getRange(1,2,lst.length,1).setValues(lst);
}

However, it doesn't generate the expected values. What should I adjust to get the correct result?

CodePudding user response：

I'm not entirely sure what your calculation is doing in mathematical terms, but I tried to reconstruct your formula and came up with the following for loop (use this instead of your while loop):

for(let i = total - 2; i >= 1; i--) {
  let e = max   i * (min - max) / (total - 1);
  lst.push([e.toFixed(2)])
}

CodePudding user response：

By formula

=arrayformula(sequence(B3 1,1,B1*B3,(B2-B1))/B3)

or, according the the precision you need

=arrayformula((sequence(B3 1,1,B1*1000*B3,(B2-B1)*1000)/B3)/1000)