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

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

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

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.
--
- K Dales


"Mike" wrote:

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