google-apps-script google-sheets random google-sheets-formula simulate

Google Sheets - How do I prevent negative values when simulating data?

Background Info

I have a maximum of 15 hours available for work Monday – Friday.
Each day, from Monday – Thursday, I will work a random amount of hours between 0 – 8.
I can work 0 hours on Monday - Thursday.
On Friday, I will work whatever hours are left from the maximum of 15 hours available.
I must work at least 1 hour on Friday.

Formulas Used

(Cell: C4) Monday: RANDBETWEEN(0-8)
(Cell: D4) Tuesday: RANDBETWEEN(0-8)
(Cell: E4) Wednesday: RANDBETWEEN(0-8)
(Cell: F4) Thursday: RANDBETWEEN(0-8)
(Cell: G4) Friday: 15 – SUM(C4:F4)
Total Hours: SUM(C4:G4)

Issues

The Friday cell will occasionally receive a negative number of hours worked.

Example Output of Issue

Output

Current Workaround

Update the values generated by RANDBETWEEN by pressing ‘DEL’ on an empty cell. This forces all the values to change.

Repeat until a positive value is received in the cell for Friday

Google Sheet Settings

Goal

Have the cells update themselves automatically IF a negative value is received in the cell for Friday

Possible Solution/Thoughts

Is there a way to force RANDBETWEEN numbers to update via a formula?

If yes, is there a way to setup a WHILE loop that will update the RANDBETWEEN values UNTIL the cell for Friday has a positive number?

Is there a way to have a script run on specific cells? The intent is to simulate data for variations on a work schedule.

I did try to accomplish this via a script but wasn’t able to get the cells to update correctly and other times it would not update at all.

function randomTotal() 
{
  var Monday = SpreadsheetApp.getActiveSheet().getRange('C4');
  var Tuesday = SpreadsheetApp.getActiveSheet().getRange('D4');
  var Wednesday = SpreadsheetApp.getActiveSheet().getRange('E4');
  var Thursday = SpreadsheetApp.getActiveSheet().getRange('F4');
  var Friday = SpreadsheetApp.getActiveSheet().getRange('G4');
  var FridayValue = SpreadsheetApp.getActiveSheet().getRange('G4').getValue();
  
  while(FridayValue < 0)
  {
    newTotal(Monday,Tuesday,Wednesday,Thursday,Friday);
    FridayValue = SpreadsheetApp.getActiveSheet().getRange('G4').getValue();
  }
}

function newTotal(Monday,Tuesday,Wednesday,Thursday,Friday)
{
  Monday.setFormula('=RANDBETWEEN(0,8)');
  Tuesday.setFormula('=RANDBETWEEN(0,8)');
  Wednesday.setFormula('=RANDBETWEEN(0,8)');
  Thursday.setFormula('=RANDBETWEEN(0,8)');
  Friday.setFormula('=15-SUM(C4:F4)');
}

Solution

This can actually be accomplished without Google Apps Script. I would suggest the following formulas for cell D4

=RANDBETWEEN(0,IF(SUM($C4:C4)<=6,8,14-SUM($C4:C4)))

You can then copy/paste this from D4 into E4 and F4 (the formula references will work), and keep C4 and G4 as is. That should do the trick!

You absolutely can accomplish this programmatically, but in general, if it's possible to do without, that's usually the simpler approach.

For a quick explanation of why this works: if the cells to the left sum to <=6, then you can always add up to 8 hours, because it leaves you in the range of <= 14 total. But, if that's not the case, you want to subtract however many hours you already have from 14, as 14 is the max you can have on Mon - Thurs, and get the remaining of at least 1 on Friday.