"What I would like this to do is check if E29 = 0 if so then G29 = 0 as
well. If not then check if this cell has a value great then 0. If it
does, keep the value. If not then choose a random number between 1 and
the value of E29." -- This is exactly what the formula you have is
doing, and it *is* calculating automatically. G29 just stays the same
once G29 returns a value >0. Perhaps you are not describing your
problem exactly? Let's step through the formula:
Start with E29=0. G29 then checks is 0>0? False, so it reverts to the
value_if_false argument, and G29 returns "0" (BTW, I'd drop the quotes
here, just to avoid any future confusion between the text 0 and the
number 0).
Enter 10 in E29. G29 then checks is 10>0? True, so it evaluates the
inner IF function. Then it checks 0>0? False, so it returns the
value_if_false argument -- a random integer between 1 and 10 (say 2).
Enter 100 in E29. G29 then checks is 100>0? True, so it goes to the
inner IF function. It then checks 2>0? True, so it returns the
value_if_true argument -- the current value in G29 which is 2. In
other words, it doesn't change, and won't change until E29 <= 0, at
which point G29 reverts to 0.
This is precisely the algorithm you described that you wanted G29 to
perform.
I'm not sure why it returns a new random number when you push F2 to
edit the cell. My guess is that the spreadsheet forgets the current
value of G29 when you push F2, the inner conditional then returns
FALSE, and you get a new random number.
My guess is that you really want a slightly different algorithm.
Something along the lines of
G29=IF(E29>0,RANDBETWEEN(1,E29),(IFG29>0,G29,0))?? This formula will
calculate a new random number as long as E29 is >0. If E29 <=0, then
it retains the current value of G29 if G29>0. Is that more like what
you want, or do you still want something different?? AT this point, I
think it's going to take a very careful description of exactly what you
want G29 to do.
