Dice must equal predefined value.

**Penelo911** · 12-21-2020, 01:34 AM

So the total of 6 dice rolls must be the value in a certain cell (B2 in my exemple). Can it refreshes until it mets the requirements? Or some other function that I don't know about?
I will link what I'm using but not knowing much I don't really understand the functions themselves, I copied them somewhere and worked with it. A way to refresh until a value appears
in a cell would fix this issue. Otherwise I tried to make is as clear as possible.

B2: 21 (Total)
C2:1 (Min value)
D2:6 (Max value)
E2:6 (Number of cells)

A4:=RANDBETWEEN(MAX($C$2,$B$2-(($E$2-ROWS($B$4:$B4))*$D$2)),MIN($D$2,$B$2-(($E$2-ROWS($B$4:$B4))*$C$2)))
A5:=IF(ROW()=$E$2+3,$B$2-SUM($B$4:$B4),IF(ROW()>$E$2+3,"",RANDBETWEEN(MAX($C$2,$B$2-(SUM($B$4:$B4)+($E$2-ROWS($B$4:$B5))*$D$2)),MIN($D$2,$B$2-(SUM($B$4:$B4)+($E$2-ROWS($B$4:$B5))*$C$2)))))
A6: drag down until there is equal to E2 (Number of cells) in a row. SO 4 more below this one.

My problem is to force one of those value to be 6. It works by himself but I tried with replacing a value with 6 and it works up until the bottom where with a forced 6 the value goes above B2 (Total) and results in an error.
I created a tickbox to refresh the page and eventually there is a value that works. If there was a way to make it auto-refresh until there is a value in said boxes that would be awesome.

Thank you!

**MrShorty** · 12-21-2020, 01:52 PM

Here's how I would probably do this:

1) I observe that "have six random integers (1-6) add up to 21 while forcing one of those integers to be 6" is the same problem as "have five random integers (1-6) that add up to 15". I change B2 so that it is 15, E2 so it is 5, and then clear B9. Now I have a list of 5 random integers (1-6) that add up to 15.
2) If order did not matter, then I could simple put the fixed 6 at the top (or bottom) of the list and be done (that would be examples 1 or 6). Since order is significant, I simply need a way to make the output cell 6 when the check box in D4:D9 is checked (D4:D9 will be TRUE/FALSE 1/0 values) and pull the other values from B4:B8. Something like =IF(D4,6,INDEX($B$4:$B$8,COUNTIFS(D$4:D4,0)) in G4 and copied into G9 should return the desired random set of numbers that add up to 21. Note that I used 1/0 instead of the check boxes in D4:D9, so I'm assuming that 0 will be a suitable equivalent to "not checked" in D4:D9. You may need to adjust this based on the actual output of your checkboxes.

Will something like that work for you?

**Penelo911** · 12-21-2020, 03:10 PM

This has been really useful. In fact I did a mistake in my explanation by trying to make it simpler to understand. The suggestion you made was in fact better than what I ask for and worked perfectly with my sheet.
Instead of 6 check boxes I have a drop-down table and according to what you select I made so it will be a 1 in D4 if the result of the drop-down table is A, 0 otherwise, 1 in D5 if the result is B and so on. The drop-down range from A to F.
I transferred that row in its own place so it's clearer and it does the job. Thank you very much ^^!