More complicated than a simple, if greater than/but less than, formula

[MarketTimer]

I am setting up a market timing study in excel and I'm having difficulty with the following:

In column c I have many rows of numbers ranging from 0 to 5. These determine if I'm long (invested) in the security or neutral (cash). For example I start long and stay long many days until a cell in column c is greater than 1.75 which triggers excel that we are out of the market and neutral the next day. The complicated part for me is writing a formula to get back long. I need the formula to only be triggered once a cell in the same column c is below .75. So, while we sell at 1.75 and we don't just buy back as soon as it is below 1.75, that would be easy, I need it to wait until a cell is below .75. This will happen many times in the column over and over, being long several days then selling and waiting to go long again.

This is my first time posting a question and I'm afraid I've rambled a bit rather than helping to explain the problem if I can help explain anything feel free to ask. Maybe the below small sampling will help:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=+IF(C3<1.75,D3*(1+B4),D3)
this is the formula I have now in column d[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 475"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Day % Change[/TD]
[TD]Long/Neutral Trigger[/TD]
[TD] Account Value[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/20/1995[/TD]
[TD="align: right"]-0.85%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/23/1995[/TD]
[TD="align: right"]-0.33%[/TD]
[TD][/TD]
[TD] $ 100,000.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/24/1995[/TD]
[TD="align: right"]0.49%[/TD]
[TD="align: right"]1[/TD]
[TD] $ 100,485.84[/TD]
[TD="colspan: 2"]All these cells the account is long as the prior day's "c" value is less than .75[/TD]
[/TR]
[TR]
[TD="align: right"]1/25/1995[/TD]
[TD="align: right"]-0.29%[/TD]
[TD="align: right"]1.5[/TD]
[TD] $ 100,193.54[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/1995[/TD]
[TD="align: right"]-0.45%[/TD]
[TD="align: right"]1[/TD]
[TD] $ 99,743.25[/TD]
[/TR]
[TR]
[TD="align: right"]1/27/1995[/TD]
[TD="align: right"]0.18%[/TD]
[TD="align: right"]1.354[/TD]
[TD] $ 99,921.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/30/1995[/TD]
[TD="align: right"]-0.93%[/TD]
[TD="align: right"]1.74[/TD]
[TD] $ 98,988.82[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/1995[/TD]
[TD="align: right"]0.45%[/TD]
[TD="align: right"]1.9[/TD]
[TD] $ 99,432.53[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/1995[/TD]
[TD="align: right"]0.41%[/TD]
[TD="align: right"]2[/TD]
[TD] $ 99,432.53[/TD]
[TD="colspan: 2"]The cells are neutral because prior day "c" value is greater than 1.75[/TD]
[/TR]
[TR]
[TD="align: right"]2/2/1995[/TD]
[TD="align: right"]0.70%[/TD]
[TD="align: right"]1.76[/TD]
[TD] $ 99,432.53[/TD]
[/TR]
[TR]
[TD="align: right"]2/3/1995[/TD]
[TD="align: right"]1.10%[/TD]
[TD="align: right"]1.5[/TD]
[TD] $ 99,432.53[/TD]
[/TR]
[TR]
[TD="align: right"]2/6/1995[/TD]
[TD="align: right"]0.88%[/TD]
[TD="align: right"]1.2[/TD]
[TD] $ 100,307.00[/TD]
[TD="colspan: 2"]These days the forumla incorrectly goes long again as "c" is back below 1.75[/TD]
[/TR]
[TR]
[TD="align: right"]2/7/1995[/TD]
[TD="align: right"]0.02%[/TD]
[TD="align: right"]1[/TD]
[TD] $ 100,322.45[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/1995[/TD]
[TD="align: right"]0.62%[/TD]
[TD="align: right"]0.8[/TD]
[TD] $ 100,940.65[/TD]
[/TR]
[TR]
[TD="align: right"]2/9/1995[/TD]
[TD="align: right"]0.10%[/TD]
[TD="align: right"]0.7[/TD]
[TD] $ 101,041.59[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/1995[/TD]
[TD="align: right"]-0.50%[/TD]
[TD="align: right"]0.1[/TD]
[TD] $ 100,536.38[/TD]
[TD="colspan: 2"]These cells should be the first long again cells as c16 (the prior day) dropped below .75[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/1995[/TD]
[TD="align: right"]-0.35%[/TD]
[TD="align: right"]0.6[/TD]
[TD] $ 100,184.50[/TD]
[/TR]
[TR]
[TD="align: right"]2/12/1995[/TD]
[TD="align: right"]1.25%[/TD]
[TD="align: right"]0.8[/TD]
[TD] $ 101,436.81[/TD]
[/TR]
[TR]
[TD="align: right"]2/13/1995[/TD]
[TD="align: right"]2.25%[/TD]
[TD="align: right"]1.2[/TD]
[TD] $ 103,719.14[/TD]
[/TR]
[TR]
[TD="align: right"]2/14/1995[/TD]
[TD="align: right"]-0.75%[/TD]
[TD="align: right"]1.85[/TD]
[TD] $ 102,941.24[/TD]
[/TR]
[TR]
[TD="align: right"]2/15/1995[/TD]
[TD="align: right"]0.10%[/TD]
[TD="align: right"]1.95[/TD]
[TD] $ 102,941.24[/TD]
[TD="colspan: 2"]Back to neutral as c21 above 1.75[/TD]
[/TR]
[TR]
[TD="align: right"]2/16/1995[/TD]
[TD="align: right"]0.33%[/TD]
[TD="align: right"]2.5[/TD]
[TD] $ 102,941.24[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ekrause]

Does this work for you? I put this formula in cell D4 and added $100,000 to cell D2 in relation to your example above.

=IF(D2=D3,IF(C3<0.75,D3*(1+B4),D3),IF(C3<1.75,D3*(1+B4),D3))

Let me know if that meets your needs or if you need something to change.

 

[MarketTimer]

That appears to have fixed my problem. I was needing the d2=d3 part, for some reason I started to do that and was mistakenly thinking I was creating a loop. Thanks very much!