Home |
Search |
Today's Posts |
#1
|
|||
|
|||
function execution
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
"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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function execution | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions |