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

MarketTimer

New Member
Joined
Sep 13, 2019
Messages
2
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:

<strike></strike>
=+IF(C3<1.75,D3*(1+B4),D3)
this is the formula I have now in column d

<tbody>
</tbody>
DateDay % ChangeLong/Neutral Trigger Account Value
1/20/1995-0.85%
1/23/1995-0.33% $ 100,000.00
1/24/19950.49%1 $ 100,485.84All these cells the account is long as the prior day's "c" value is less than .75
1/25/1995-0.29%1.5 $ 100,193.54
1/26/1995-0.45%1 $ 99,743.25
1/27/19950.18%1.354 $ 99,921.00
1/30/1995-0.93%1.74 $ 98,988.82
1/31/19950.45%1.9 $ 99,432.53
2/1/19950.41%2 $ 99,432.53The cells are neutral because prior day "c" value is greater than 1.75
2/2/19950.70%1.76 $ 99,432.53
2/3/19951.10%1.5 $ 99,432.53
2/6/19950.88%1.2 $ 100,307.00These days the forumla incorrectly goes long again as "c" is back below 1.75
2/7/19950.02%1 $ 100,322.45
2/8/19950.62%0.8 $ 100,940.65
2/9/19950.10%0.7 $ 101,041.59
2/10/1995-0.50%0.1 $ 100,536.38These cells should be the first long again cells as c16 (the prior day) dropped below .75
2/11/1995-0.35%0.6 $ 100,184.50
2/12/19951.25%0.8 $ 101,436.81
2/13/19952.25%1.2 $ 103,719.14
2/14/1995-0.75%1.85 $ 102,941.24
2/15/19950.10%1.95 $ 102,941.24Back to neutral as c21 above 1.75
2/16/19950.33%2.5 $ 102,941.24

<tbody>
</tbody>


<tbody>
</tbody>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

ekrause

New Member
Joined
Aug 7, 2019
Messages
49
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

New Member
Joined
Sep 13, 2019
Messages
2
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,172
Messages
5,640,580
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top