The setup is currently quite simple:

In r1c4:
=IF(R1C1=TRUE,RANDBETWEEN(R1C2,R1C3),””)

I have this cell as part of a vertical array of identical functions that may or may not be TRUE.
So immediately adjacent below are 11 more rows identical to the example above; then r12 will sum elements of the result.

What I need is to return my random number in r1c4; and re-randomise it only if r1c1 is toggled false then true again; which it currently does do; but I need to prevent the RAND function re-calculating upon toggling true any of the other TRUE/FALSE Cells in r2-11,c1 of the array; so that the 1st random number it generates when toggled is calculated by the sums in r12.

For clarity’s sake, the intended use is that an end-point user will:

1. Toggle r1c1 to True and r1c4 will generate the random value between r1c2 and r1c3;
2. The user at their discretion may choose to toggle true any subsequent cells from r2c1 to r11c1 to also generate numbers across r2c4 to r11c4; without recalculating the value of r1c4, or any others.
3. The sum of r1c4 to r11c4 is returned in r12c4; without the values changing as more are entered.

It’s imperative that if a value has been generated into r1c4, it not then change from that point unless r1c1 is set to false.

First time posting. Please let me know if I need to change anything or can explain it in more detail.

I’m quite stumped. I’ve thought about a few different ways but there must be a simpler way than trying to build my own random number generator that behaves differently to the RAND function.

I tried turning off automatic calculations and manually recalculating the sheet but that breaks the sums in row 12, and it’d be too many extra steps for the end point users to manage reliably, and will break many other elements across the sheet that should be automatically calculated.

My Other thought was to have the formula always be recalculating all 11 rows, but only make them visible when that row is toggled true, but that doesn’t solve the problem of needing each row to not recalculate the others when toggled, and the sum in row 12 would have to exclude rows that are currently False; which wouldn’t be too hard, but then how would I keep the sum from recalculating when any of the values are made visible?

I would try VBA or scripts, and Macros; but I need to try and find a solution that works on Excel, Excel in browser, OpenOffice, and G-Sheets; so I’m desperately looking for a formulaic solution over excluding any of those platforms.