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

#### MarketTimer

##### New Member
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:

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

<tbody>
</tbody>
 Date Day % Change Long/Neutral Trigger Account Value 1/20/1995 -0.85% 1/23/1995 -0.33% \$ 100,000.00 1/24/1995 0.49% 1 \$ 100,485.84 All 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/1995 0.18% 1.354 \$ 99,921.00 1/30/1995 -0.93% 1.74 \$ 98,988.82 1/31/1995 0.45% 1.9 \$ 99,432.53 2/1/1995 0.41% 2 \$ 99,432.53 The cells are neutral because prior day "c" value is greater than 1.75 2/2/1995 0.70% 1.76 \$ 99,432.53 2/3/1995 1.10% 1.5 \$ 99,432.53 2/6/1995 0.88% 1.2 \$ 100,307.00 These days the forumla incorrectly goes long again as "c" is back below 1.75 2/7/1995 0.02% 1 \$ 100,322.45 2/8/1995 0.62% 0.8 \$ 100,940.65 2/9/1995 0.10% 0.7 \$ 101,041.59 2/10/1995 -0.50% 0.1 \$ 100,536.38 These 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/1995 1.25% 0.8 \$ 101,436.81 2/13/1995 2.25% 1.2 \$ 103,719.14 2/14/1995 -0.75% 1.85 \$ 102,941.24 2/15/1995 0.10% 1.95 \$ 102,941.24 Back to neutral as c21 above 1.75 2/16/1995 0.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
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
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!

Replies
3
Views
979
Replies
2
Views
115
Replies
10
Views
626
Replies
2
Views
159
Replies
6
Views
246

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.

### Which adblocker are you using?

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

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