How to lock a cell value when values going into that cell keep changing...

[momentumtrader]

Hi everyone -

First time trying to use google groups, so thanks in advance for any
help with this task I am trying to work out. I have a real time
spread sheet that gets live market data. I need to be able to write
if then statements but when an occurence happens *ONE* time the cell
will return a value even if the data going into that cell would make
it change.

so for example, say i want to know if x>100 then return a "1" in that
cell. what is happening now is that the real time data going into
that formula could be x,97, 98, 101, 103, 96, 101, 98, etc. So the
value of that cell will keep changing from 1 to 0 ( the if false value
to be returned).

what i am trying to do is that once the cell (x > 100) it will then
return a 1 and *NOT* fluxuate. I can then use a macro/button to reset
things when i have completed an action (or taken a trade).

Thanks for any help with this area, it is really apprecaited!

Regards,

D. Eli
Momentumtrader

 

[JE McGimpsey]

One way:

Choose Tools/Options/Calculation and check the Iteration checkbox.

Then, assuming cell B1 is monitoring cell A1, then enter in B1:

=IF(B1=1,1,--(A1>100))

 

[momentumtrader]

One way:

Choose Tools/Options/Calculation and check the Iteration checkbox.

Then, assuming cell B1 is monitoring cell A1, then enter in B1:

=IF(B1=1,1,--(A1>100))

WOW! that was fast, thank you very much. i have i just tried it
out. I will have to test it tomorrow with live datafeed to see what
happens. i really apprecaite your help with this, it was making me
crazy trying to find a solution!

 

[momentumtrader]

One way:

Choose Tools/Options/Calculation and check the Iteration checkbox.

Then, assuming cell B1 is monitoring cell A1, then enter in B1:

=IF(B1=1,1,--(A1>100))

This was super helpful, thank u...

How do i reset it then to go back to the state where it is looking for
x>100.

So for example, i want the cell to = 1 and stay in that condition when
looking for x > 100. after that has occured, i will be doing
something, trading, etc. once that trade is complete we then are
looking once again for when x > 100. can i add a button that resets
this so it is looking for a new iteration (or something like that).

thanks!

 

[JE McGimpsey]

One way:

Record a macro while you enter the formula (with the source cell < 100
of course).

Another possibility, if your valid changing values will never be
negative:

=IF(A1<0,0,IF(B1=1,1,--(A1>100)))

Then just enter a negative number in A1.

 

[momentumtrader]

One way:

Record a macro while you enter the formula (with the source cell < 100
of course).

Another possibility, if your valid changing values will never be
negative:

=IF(A1<0,0,IF(B1=1,1,--(A1>100)))

Then just enter a negative number in A1.

This second solution that you offered with just using a negative
number in the A1 cell will work perfectly. Thank you so much for your
assistance. I am new to coding in excel, we do most of our trading
coding in a program called TradeStation. But we are now moving our
analysis (systems and indcators) into excel, so this is a new area for
us. Please let me know if you offer consulting services, as we might
have projects coming up in the future. Again, really appreciate the
help!