How to add a random number to each cell of a column?

Question

I am sure this is a rather naive question, but I have never done this before, so please bear with me. I have an excel sheet with multiple columns. For one of the columns, I have to add a random number to each cell of this column.

eg.

Col1 Col2 45 12 34 34 12 12 56 78 78 30 

Now in the second column I have to add something random, say, from 0 to 10. I can’t put completely random values ​​in the second column, since all I want is a slight deviation from the existing values.

I know how to create random numbers using RAND() or RANDBETWEEN() . I also know that I can convert it to an integer using ROUNDUP() and then SUM() . But when I try to do this with a formula, it gives me a circular reference warning, which is correct.

Can anyone help with this? Your help is greatly appreciated.

Answer 1

You cannot link to B2 inside cell B2 without getting a circular link. If you do not want to "hard code" the value for each cell, for example. in B2 =12+RAND() , in B3 =34+RAND() .... I suggest you use the third column with this formula or similar in C2 copied down

=B2+RAND()

Answer 2

Something like (for the second column):

 =A2-5+RANDBETWEEN(1,10) 

. -5 is added if you want both less and more than the number in the first column