Search code examples
excelrandom

Randomize a value based on another cells value within 20%

I am mocking up patient fall data by age group for a sample report from an Excel dataset. What I'm trying to do is create a random value based on the cell contents from another cell. Cell D4 contains the value of 0.7% I want cell E4 to be a random value +-20% of D4. No other constraints exist. Thank you, Doug

I did try variations of randbetween and rand but can't sort out how to do it based on another cell value.

Solution

  • Everything depends on decimal precision you want.

    Lets take your number 0.7

    -20% will be 0.56 and +20% will be 0.84

    So you are seeking a random number between 0.56 and 0.84. While 0.64 would be desired output I believe 0.64321321456844 would not. And there are infinite decimal values between any 2 numbers.

    RANDBETWEEN works only with integers, but there is a workaround.

    Multiply RANDBETWEEN arguments by 10 power of decimal places (2 in this case) and divide result by same value (10 power of 2 = 100):

    =RANDBETWEEN(0.56*100,0.84*100)/100

    Random number will be with max 2 decimal places:

    enter image description here

    Lets make it more universal.

    To find how many decimals there are use =LEN(TEXTAFTER(0.56,".")) and =LEN(TEXTAFTER(0.84,".")). Get max value with MAX formula and use it in POWER formula:

    =LET(a,A2,b,a*0.8,c,a*1.2,d,MAX(IFERROR(LEN(TEXTAFTER(VSTACK(b,c),".")),0)),e,POWER(10,d),RANDBETWEEN(b*e,c*e)/e)

    Result:

    enter image description here

    Also keep in mind that Excel decimal precision is limited to 15 digits.