Ask Excel for a column of data, how to add and subtract randomly

The formula is changed to: i26+round(rand()*3). Formula Teardown Instructions:

1. rand() generates a random number between 0-1.

2. rand()* multiplied in order to produce a random number between one;

3. rand()*subtract is to generate a random number between to;

4. round(rand()*3) Use the round function to keep a random number with 3 decimal places.

5. i26+round(rand()*3) Add the original data with this random number is equivalent to what the landlord saidRandom addition and subtraction (Numbers in a range form new numbers.)Finish.

i26+(-1) *randbetween(1, 2) *randbetween(1, 300) /1000

It should be the number that is strictly between plus or minus plus the original number.

1. The office software used in the demonstration is Office Excel, the version of the software is Office Home and Student Edition 2016, and other versions of Office or WPS** can refer to this operation.

2. According to the problem description, we enter a fixed value in cell A1.

3. Then we input the function to get the random value in any cell, "=rand()*24000-12000", and the value range of the function is -12000 +12000.

4. Then we use a1 plus random numbers in the range to realize that we will add a value in the range and enter "=a1+d4" in any cell, where the cell content of d4 is the value in the random range.

5. Click enter after the input is completed, you can see that we have got a fixed value and randomly add a range of values, because it is a random value function, so every ** change will change the value of the cell.

1. Enter the random function formula =rand() in a blank cell to get a random value.

2. If you need to set it to randomly add or subtract between the number 2 and the number 10, you can input the random function: =rand()*10-2)+2.

3. Click enter, you can generate the calculation result of the input formula, and you can see in the figure below that the generated random data is between 2 and 10.

4. Double-click the formula cell to generate the result, and you can see that the numerical value within the range of reduced numbers has been generated at this time.

Random requirements still need to use random functions, but some processing needs to be done:

Generate random values with random functions;

Random values are judged by conditional functions; (For example, we stipulate that when the random value is equal to 1, add it, otherwise it will be subtracted).

Example: c1 = if(randbetween(0,2)=1,a1+b1,a1-b1).

Your idea may be: b1=b1+a1 or b1=b1-a1, but this is not recommended by default in excel, cells cannot refer to themselves (you can write this after turning on the setting, but circular references will affect the calculation results and consume hardware resources), so you can only do this in other cells, in this example c1 is used to store.

1.On the basis of the above step, add a random reference and use indirect() to generate random cells.

Example but not perfect: cell a1 style reference byLetters + numbersRandom functions can only generate numbers, and letters can't do anything. We need to change that to use the r1c1 style:

There are tens of thousands of cells, so if you really want to do that, remember to narrow the reference area as much as possible! After all, there is no point in adding or subtracting 0.

Points to look out for:

**There is an upper limit to the maximum number of columns and rows supported, and the maximum value in the random function cannot exceed the maximum row number and maximum column number.

Then there's the question of probability.

Add or subtract any value in several cells randomly in a value in Excel Enter or copy-paste this formula in C1.

Randomly add or subtract any of the values from several cells in column A from B1.

Randomly add or subtract from B1 the sum of the values in several cells in column A.

randbetween(1,2)

For example, if a number is a1 and b1 is to be added or subtracted randomly, the formula can be.

a1+if(rand()>1,-1)*a2or. =a1+a2*-1^(rand()>

Something like this is fine.

If you want to choose a random number from 0 to 100, the formula is: =randbetween(0,100).

Dear, the formula is changed to:

a1+rand()*24000-12000

This results in additions and subtractions within -12000 +12000.

What you mean by this formula is that if you want to add or subtract any value from 0 to 12000 a1, you can use randbetween, and if you want an integer, then :

a1+randbetween(-12000,12000), it doesn't have to be that complicated.

If you want to add or subtract any decimal place in this interval (1 digit for example) then:

a1+randbetween(-120000,120000) 10 or.

round(a1+randbetween(-12000,12000)+rand(),1)