Google-sheets – How to select random cells in a row that add up to 300, and count how many it took

google sheets

I have a column that contains a bunch of random integers, I would like to select a number of those cells until they add up to, or just over 300, then output the count of how many rows it took to make 300.

Col 1 | Col 2
-------------
38    | =countOfRandomlyPickedRowsFromCol1ThatAddUpTo300()
44    |
32    |
78    |
12    |
2     |
..    |
23    |
2     |

Update: ended up writing this, does what I need. 

function getTotalWeeks( runCount ) {

  // Make is sleep
  Utilities.sleep(1000)

  // Get the spreadsheet
  var spreadsheet = SpreadsheetApp.getActive();

  // Do we have runCount?
  var runCount = (runCount === undefined ? 999 : runCount)

  // Get the vars
  var vars = spreadsheet.getSheetByName('Variables');
  var total_stories_r = vars.getRange('E7');
  var total_stories = total_stories_r.getValue()

  // Get the week sims
  var monte = spreadsheet.getSheetByName('Montecarlo run');
  var possible_velocity = monte.getRange('A2:A');
  var data = possible_velocity.getValues();


  var weeks_arr = []

  // Print for the run count
  for (var ii = 0; ii < runCount; ii++) {
    var total = 0
    for(var weeks = 0; weeks < data.length; weeks++) 
    { 
      var element = data[Math.floor(Math.random() * data.length)]; // Random elent from Array
      total = +total + +element // + unary operators to convert strings to ints
      if (total >= total_stories) {
        var bollocks = []
        bollocks.push(weeks)
        weeks_arr.push(bollocks)
        break;
      }
    }
  }

  // Put it all back
  var range = monte.getRange("B1");
  range.setValues([["Number of weeks"]])

  var range = monte.getRange("B2:B1000");
  range.setValues(weeks_arr);


}

Best Answer

If A2:A had random integers, 

=SUMPRODUCT(SUMIF(ROW(A2:A),"<="&ROW(A2:A),A2:A)<300)+1
  • SUMIF to cumulatively add A2:A
  • SUMPRODUCT to add TRUE's,IF SUM<300
  • A2:A is added in first row to last row order. If you want to add randomly/in any other order, You'll need to write a custom script.
Related Topic