0

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);


}
2
  • 1
    You'll need to write a script
    – TheMaster
    May 1 '18 at 15:27
  • You can submit the script as an answer and accept it.
    – Jerry
    May 3 '18 at 8:54
0

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.
0

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);


}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.