adjusting datasets

I wish to adjust a number of datasets; is it possible to have an excel formula to do the following?

If X is >= 0.049 then deduct a random number from X so that X is in the range 0.020 to 0.049. Thanks.

Hi Paul

Is this what you are after?

=IF(B23>=0.049,B23-RANDBETWEEN(0.02,0.049),B23)

B23 is the cell containing the X value in your example.

Windy

Windy

Thanks. not quite.

I have tried it but it is returning negative values when the X cell value is greater than 0.049.

eg

0.028 0.028
0.034 0.034
0.047 0.047
0.053 -0.947
0.058 -0.942
0.065 -0.935
0.069 -0.931

the new value should also be between 0.02 to 0.048.

Regards

Paul

Originally Posted by PaulALC

If X is >= 0.049 then deduct a random number from X so that X is in the range 0.020 to 0.049. Thanks.

Couldn't you just set it to a random number between 0.020 nd 0.049 directly?

eg

=IF( "X" >= 0.049, RANDBETWEEN(0.020, 0.049), "X" )

Ben

I have tried that but am getting the following 1.000 in the third column using your suggestion.

0.052 -0.948 1
0.026 0.026 0.026
0.024 0.024 0.024
0.035 0.035 0.035
0.062 -0.938 1
0.052 -0.948 1
0.057 -0.943 1
0.027 0.027 0.027
0.037 0.037 0.037


Regards

Paul

Paul

If you can add a column that contains the numbers between 0.020 & 0.049 (this can be hidden)

You could then try this:

=IF(A1>=0.049,INDIRECT("V"&RANDBETWEEN(1,29)),A1)

The A1 is the number you are testing.
The "V" is the column containing the numbers between 0.020 & 0.049.
The 1,29 refers to the rows used.

The problem I can foresee is it will recalculate when something changes.

Windy

Oh, right, RANDBETWEEN only does integers. I forgot about that.

Use this instead.

=IF("X" => 0.049, RAND() *0.029 +0.02, "X")

Sorry Windy

I cannot see the wood for the tress. I have attached an example sheet.

Regards

Paulbook 1 data.xlsx

Paul

I know what you mean. I looked at your example for ages before I saw the problem.

Your formula is missing a set of quotation marks

=IF(D4>=0.049,INDIRECT(M&RANDBETWEEN(4,32)),D4)
it should read...
=IF(D4>=0.049,INDIRECT("M"&RANDBETWEEN(4,32)),D4)

Windy

Thanks Windy, Thanks Ben.