I want to calculate a random value for each positive number in a column, but I want the value to be placed in a 10% range, so it can only be 10% higher or lower than the original value.

The result must be greater than zero, which is the second prerequisite.

I attempted a formula that looked like this, but it appears to be incorrect because it occasionally returns values that are lower than zero, which contradicts the second condition.

`=IF(RAND()<0.5,(B2+(B2/100)*90)-(RANDBETWEEN(B2-0.1,B2+0.1)),(B2-(B2/100)*90)+(RANDBETWEEN(B2-0.1,B2+0.1)))`
Dec 25, 2022 in Others 560 views

## 1 answer to this question.

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if you are only working with integers and your value is in B2? Since 0.9 * a positive number is still a positive number, this cannot return a negative number if B2 is positive.

If you require floating point numbers, you can create a random number between 0 and 1 using the RAND() function, then divide it by 5 and remove 0.5 to get a number between -0.1 and 0.1. Then, to produce a result between 0.9 and 1.1 times the original number, add 1 and multiply that result by the original value:

`=(1+(RAND()-0.5)/5)*B2`
• 63,700 points

## Excel-How can I get the address of a cell instead of a value?

There are various difficulties in this. Which ...READ MORE

## How to programmatically get the values of a spilled Excel range in VBA?

By using the Text property, I was ...READ MORE

## SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I ran into the same problem due ...READ MORE

## How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE