Need Help With A Limited Random Array

[BlackLabel]

I’m trying to generate an array of 12 random integers, between 0 inclusive and a number referenced externally (math or template column) Let’s use 4. The Sum of the array numbers must equal exactly another specific number. Let’s say 20.

So we have 20 points to allocate, and each number can’t exceed 4. This would be an example of a desired output:

2,0,3,3,1,4,4,1,0,2,0,0

I am absolutely stumped on how to efficiently do this, as in 13 columns or less. I’m currently able to do this acceptably with another array, that don’t have a digit caps, but when i introduce the cap, it goes over my head. I have a functioning excel formula, but it utilizes MAX() and that is not valid in the excel column. I’m not opposed to trying to do some sort of javascript, but I need this to be compatible on phones, and i’ve had horrible luck getting JS columns to function correctly on mobile (iPhone)

Here is the one that worked alright in excel but doesn’t work in the Glide Excel column. Also it would have to shorten the random range ramping up near the final column to account for the possibility of rolling all 0’s up to a point when it would not be able to hit the exact original number.

A1 = 20 (Points Available)
A2 = 4 (Integer Cap)

A3 = RANDBETWEEN(0,A2)
A4 = RANDBETWEEN(0,A1-MAX(A3,(A1-A2)))
A5 = RANDBETWEEN(0,A1-MAX(SUM(A3:A4),(A1-A2)))
A6+ = Just increase the range of the MAX(SUM()
…
A14 = A1-SUM(A3:A13)

I know this isn’t a glide specific question and this isn’t necessarily a math forum, but I did want this solution to be accomplishable within glide’s available column options and supported excel functions if possible.

[Jeff_Hager]

Glide does have an Excel formula column you can try, but I would probably use javascript. I don’t know why you say you have trouble with javascript columns because javascript is pretty universal. I don’t know why it would be a problem with iPhone.

Anyway, I threw the first paragraph of your question at chatGPT and this is what it spit out. (I have not tested it, so I don’t know if it’s what you want. If not, you could try asking ChatGPT yourself with a little more explanation.) I modified the code to return a value in Glide. You just need to plug in the p1 and p2 parameters. Try it and see what you get.

// Function to generate an array of 12 random integers function generateRandomArray(externalNumber, desiredSum) { // Generate an array of 11 random integers between 0 (inclusive) and externalNumber const randomNumbers = Array.from({ length: 11 }, () => Math.floor(Math.random() * (externalNumber + 1)));

// Adjust the last element of the array to meet the desired sum
randomNumbers.push(desiredSum - randomNumbers.reduce((acc, curr) => acc + curr, 0));

return randomNumbers.toString();
}

//p1 = externalNumber
//p2 = desiredSum
return generateRandomArray(p1, p2);

[Jeff_Hager]

This might work better. Still haven’t thoroughly tested it but seems to be working.

function generateRandomArray(externalNumber, desiredSum) {
  let randomNumbers = Array.from({ length: 12 }, () => Math.floor(Math.random() * (externalNumber + 1)));

  // Calculate the sum of the generated numbers
  let currentSum = randomNumbers.reduce((acc, num) => acc + num, 0);

  // Adjust the last element until the sum matches the desired sum
  while (currentSum !== desiredSum) {
    const indexToAdjust = Math.floor(Math.random() * 12);
    const difference = desiredSum - currentSum;

    // Ensure the adjustment doesn't make the number negative
    const adjustment = Math.min(Math.max(0, randomNumbers[indexToAdjust] + difference), externalNumber);

    currentSum += adjustment - randomNumbers[indexToAdjust];
    randomNumbers[indexToAdjust] = adjustment;
  }

  return randomNumbers;
}

function isNumeric(value) {
    return !isNaN(parseFloat(value)) && isFinite(value);
}

// Return 0 if either value is not a number
if (!isNumeric(p1) || !isNumeric(p2)) {return 0;}
// Return 0 if external numbers can't add up to desired number
if (p1*12 < p2) {return 0;}

return generateRandomArray(parseInt(p1),parseInt(p2)).toString();

[Jeff_Hager]

I’ve updated the code above. Seems pretty stable. I added some error handling because it was causing some issues without it.

• No number can be negative.
• If the numbers can’t possibly add up to the desired sum then it just returns 0.
• If either input is not a number then it returns 0.

Hopefully it works for you.

[BlackLabel]

I really appreciate it. This works great!