0
228
ViewsHow can I create an array of sequential values through the input of the minimum and maximum values?
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?
·
Santiago Gelvez
2 answers
Answer question0
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)
·
Santiago Gelvez
Report
0
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)])
}
·
Santiago Gelvez
Report
Answer question