Will two RAND() functions in the same formula produce two separate random numbers?

I am reviewing someone else's work without them being easily able to answer. The formula below should not produce results less than zero as it is related to a physical quantity that cannot be negative. However it is:

=IFERROR(IF(((RAND()-0.5)*J6*I6)<0,0,(RAND()-0.5)*J6*I6),0)

Both the column "J" and "I" numbers are all positive. I assume it is because the two RAND() functions are separate from each other and therefore are in fact two different random numbers each time it calculates. Is this the case?

In which case a separate column with the RAND() functions and this one referencing them would work?

Solution 1:

To answer the question, I wrote a sum of two random integer numbers in Excel. The result would be always even, if the random number generator would re-use the same value in a given expression. But it isn't!

So, your assumption is correct: Each call provides a fresh independent value.

To use a separate cell would in fact put the same value twice into your expression. You could extract the complete sub-expression (RAND()-0.5)*J6*I6) to improve readability of your formula.

Solution 2:

You can try to put in a cell RAND()-RAND(). You will see that the value is not null. Then RAND is called two time and return two different values.