Random number within a specified percent of another number

En4sir · Dec 11, 2020

Good morning,

I am trying to generate random numbers but with a bit of a twist.

range A1 thru A10 had a set of numbers
I would like range B1 thru B10 to have random numbers, but to be within a specified percentage of the value in the A range.
And then the same for C1 thru C10.
The percentage would allow the number to be random in both directions .. plus or minus.

I have tried using the randbetween to get this to work, but I cant figure out how to get the plus or minus percent value
Can anyone please help me with this?

Thanks
Mark

Toadstool · Dec 11, 2020

Hi Mark,

Not sure my O Level maths holds up but I think it's:

En4sir.xlsx

A

B

C

1

1.012

1.0010

1.0790

2

3.651

4.2100

3.6280

3

4.526

4.5020

3.6920

4

-2.589

-2.8410

-3.4060

5

3.103

3.3740

3.3150

6

1.792

1.5400

1.7600

7

2.111

1.7330

1.8600

8

3.234

3.4090

3.4180

9

-1.133

-1.0700

-0.9610

10

-0.879

-0.7830

-0.6750

11

12

13

14

20%

Sheet1

Cell Formulas
Range	Formula
B1:C10	B1	=SIGN(A1)((RANDBETWEEN(ABS(A11000)-((ABS(A11000)$A$14)),ABS(A11000)+((ABS(A11000)*$A$14)))/1000))

En4sir · Dec 11, 2020

Toadstool said:
Hi Mark,

Not sure my O Level maths holds up but I think it's:

En4sir.xlsx
A B C
1 1.012 1.0010 1.0790
2 3.651 4.2100 3.6280
3 4.526 4.5020 3.6920
4 -2.589 -2.8410 -3.4060
5 3.103 3.3740 3.3150
6 1.792 1.5400 1.7600
7 2.111 1.7330 1.8600
8 3.234 3.4090 3.4180
9 -1.133 -1.0700 -0.9610
10 -0.879 -0.7830 -0.6750
11
12
13
14 20%
Sheet1
Cell Formulas
Range Formula
B1:C10 B1 =SIGN(A1)*((RANDBETWEEN(ABS(A1*1000)-((ABS(A1*1000)*$A$14)),ABS(A1*1000)+((ABS(A1*1000)*$A$14)))/1000))