Hey,

I am just wondering if there is anyway to get my cells with if
statements in the to execute automatically. They are dependant on
other fields and I have an if statment that say 'if there is no value
do X if there is a value do Y' and it will not execute I have to double
click on the cell then hit enter and it will execute. Can you use loops
or anything in excel (As you can see I am not very familiar with excel)


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=377742

Are you certain you don't have the calculation option (Tools - Options)
set to manual? My IF functions always calculate automatically.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=377742

Go to Tools Options. Calculation tab. Select Automatic under
calculation.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377742

nope it is set to automatic...an other ideas why this might be
happening?


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=377742

my cell that does simple multiplication occurs automatically...just the
ones with IFs don't


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=377742

can you post your formula and the values in the concerned cell.

Mangesh


"matty_g" wrote in
message ...

my cell that does simple multiplication occurs automatically...just the
ones with IFs don't


--
matty_g
------------------------------------------------------------------------
matty_g's Profile:
http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=377742

Can you give us more detail? Specifics of the formula/spreadsheet?
What it actually does vs. what you expect it to do? Is this the same
spreadsheet that we turned iteration on to get your one IF function
working? Excel has an "evaluate formula" tool (Tools - Formula
Auditing - Evaluate formula) that may give some clues as to what your
problem is.

Just as a test, I put C7=IF(B7=1,"yes","no") into a spreadsheet.
B7=A5/B5. Everytime I change the values in A5 or B5, the IF function
evaluates. Of course, the function only returns "yes" when A5=B5, but
it will change from yes to no without any further prompting from me.
Can you set up a blank spreadsheet to do the same thing?


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=377742

OK the thing is when i do a simple if like Mr. Shorty suggested it works
when I change the value of the cell in question the code gets executed.
I am not sure why this other code won't here is what I have and I will
explain what I would like to happen.

=IF(E290, IF(G290,G29,RANDBETWEEN(1,E29)), "0")

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.

Hope this helps out a bit more. The thing is it works fine but it just
doesn't work automatically.


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=377742

"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 00? 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 100? True, so it evaluates the
inner IF function. Then it checks 00? 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 1000? True, so it goes to the
inner IF function. It then checks 20? 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(E290,RANDBETWEEN(1,E29),(IFG290,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 G290. 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.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=377742

I will try out yours and see if it works for me, I know that my
algorithm does exactly what I want. The only problem is I want it to
choose the number once I enter a value into E29 and it is not doing
that. I have to edit the cell then hit enter then it will select one.
But maybe it will do this your way I will try it and post the results.

Thanks for the help


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=377742

GREAT!!

for some reason your formula calculates it automatically. Thanks a
lot.

FYI - yes it is the same spreadsheet that you gave me the iteration tip
on

Thanks for both!!


--
matty_g
------------------------------------------------------------------------
matty_g's Profile: http://www.excelforum.com/member.php...o&userid=23954
View this thread: http://www.excelforum.com/showthread...hreadid=377742

Glad it's working for you.

I think I figured out why your version wouldn't work. I alluded to it
when I suggested parenthetically to get rid of the quotes around the 0.
The text value 0 "is greater than" the number (or any other number.
Try putting the boolean formula "0"0 into a cell and see what you
get). So the function initially returned "0" (rather than 0). Then on
subsequent calculations, when it checked the G290, it always returned
true, so it never changed. Again, I guess that the act of editing the
cell by pressing F2 caused it to forget its original value, and then
the condition could return FALSE.

It can be an obnoxious thing that distinction between text and number.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=377742