Setting the minimum and maximum value and creating an array with a sequence of X values between them-CodePudding

Using interactive calculation (circular reference) in Google Sheets configs to be able to generate a sequence of 100 numbers that follow a pattern of growth defining the minimum value and the maximum value, I do it like this:

B1 → 0.01

B2 → =ARRAYFORMULA(VALUE(TEXT(INDIRECT("B"&
 MAX(IF(B3:B<>"", ROW(B3:B), ))) SORT(ROW(INDIRECT("B1:B"&
 MAX(IF(B3:B<>"", ROW(B3:B), ))-2)), 1, 0)*(B1-INDIRECT("B"&
 MAX(IF(B3:B<>"", ROW(B3:B), ))))/(
 MAX(IF(B3:B<>"", ROW(B3:B), ))-1),"0.00")))

B100 → 35.70

To remove these calculations from the spreadsheet, I'm trying to convert the idea in Google Apps Script in order to send the values to the spreadsheet.

Sp I'm trying to generate an array with that same idea → a total of 100 numbers where the minimum and maximum are defined by me and the other 98 numbers must be between the minimum and the maximum keeping a growing pattern:

[[0.01],
[...],
[...],
[...],
[35.70]]

In my attempts I tried to generate like this:

function between() {
  const min = 0.01;
  const max = 35.70;
  const total = 100;
  const seq = (min   max) / total;
  const lst = [[min]];
  let i = 1;
  while (i <= 98) {
    lst.push([(seq*i).toFixed(2)])
    i  
  }
  lst.push([max])
  SpreadsheetApp.getActive().getSheetByName('data').getRange('A1:A100').setValues(lst);
}

But mathematically it doesn't have an exact pattern of 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)