Return value of first argument of condition without recalculation-CodePudding

I know I can solve it by using a separate cell and make it invisible but I'd like a clean solution for this and use only 1 cell and 1 formula.

For this example I need to get a random number between X.00 and Y.00 (with decimals), not lower than 0.00 and not superior than 9.00. But for this example I am using only 1 condition for avoiding values less than 0.00 to simplify.

I will use RANDBETWEEN(). The X and Y are supplied by 2 other cells on the dummy file bellow: B3 and D3.

The reason why sometimes the random number can be less than 0.00 or above 9.00 is that I need the random result to be between random /- 1 than (X Y)/2.

Also X and Y values will vary. Sometimes X will be 3.54, sometimes 8.99 and same for Y. For situations when it happens that X and Y are a low number like 0.5 the RANDBETWEEN() function might output a negative number. When so I need the output to be 0.00. Same for high values. If both X and Y will be close to 9.00 the result might be something like 9.35. In this cases I´d need the output to be 9.000. But in the below formula I am only using cases below zero to make it simple.

So the problem I am unable to resolve is that I need to get the value of the fist argument of the ÌF() condition without recalculating it. If I refer to the 3rd argument for FALSE then I will recalculate. my formula is this:

=IF(
    (RANDBETWEEN(
        ((((B3*100) (D3*100))/2)-100),
        ((((B3*100) (D3*100))/2) 100))
        /100)<0,0,
    (RANDBETWEEN(
        ((((B3*100) (D3*100))/2)-100),
        ((((B3*100) (D3*100))/2) 100))
        /100))

So this will check if the first argument is less than 0.00, if it is it displays 0.00 if not it recalculates again and this is a problem because it might sometimes recalculate a value less than 0.00.

My question is: Is there a way to return the value of the first argument of the condition without recalculation of RANDBETWEEN() and without using a separate cell?

If not possible I would also welcome any solution using custom GAS functions.

My dummy file:
https://docs.google.com/spreadsheets/d/15YtgUVqDTuC-raMNJiN-YG4j3URaorXdPiwTy_Kb_K0/edit
(click checkbox to reset and again to recalculate the random number).

CodePudding user response：

Wrap your RANDBETWEEN within a MIN - MAX

=MAX(
  MIN(
   RANDBETWEEN(((((D3*100) (B3*100))/2)-100),((((D3*100) (B3*100))/2) 100))/100,
   9
  ),
 0
)*A1