Getting RAND() value but NOT its Function?!?

[Mike]

Hi everyone,

Say I am using a linear term such as " b * X "; where "b" is a
parameter while "X" is a variable...

Now, assume that "b" is RAND() value and assume I am using an excel
optimizer. When I run the optimizer, it complains that the problem
doesn't satisfy linearity conditions; because RAND() is a function so
it turns "b * X" to become non-linear!!!

I tried to go around this by creating another cell which copy and
value-paste the RAND() value into it. It solves the problem, but now I
have different problem: when RAND() value is copied and value-pased
and, by the time it is put in the new cell, the original cell which has
RAND() function changes its value.....and so on!!

How can I go around this correctly, so I keep the model linear and keep
RAND() values in both cells same?

Thanks,
Mike

 

[Guest]

The RAND() function is problematic this way since it recalculates with the
other cells, and you need the other cells to recalculate so you cannot keep
the random cell from a recalc. You could turn calculation to manual, put
your RAND() function in a cell on a different sheet, and then use the "Calc
Sheeet" (in Options... Calculation) but that is a cumbersome workaround.

Instead, don't use the Rand Function but use this VBA code attached to a
Command Button:
Public Sub RandomValue()
Randomize
Range("A1") = Rnd()
End Sub

This will feed a new random value into A1 when - and only when - you press
the button.