Excel 2007 : Holding a Value in Excel

**Caliwowe** · 06-23-2010, 08:34 PM

I was hoping to get some help with one of my school projects.

We are doing a Technical Analysis of a stock (mine is Devon Energy DVN). I took the daily adjusted close of the last three years, and have calculated the 10-Day Moving Average and the 30-Day Moving Average. From there our Recommendation simulator is based the on the following requirements.

1. For a "Buy" recommendation the 10-Day Moving Average (MA) but be increasing; The 30-day MA must also be increasing; and the 10-Day MA must be greater than the 30-day MA. These are denoted by 1's for true and 0's for false. If all are true (all 1's) then it is a buy recommendation.

2. For a "Sell" recommendation it is the complete opposite. The 10-day MA must be decreasing; the 30-day MA must be decreasing; and the 30-day MA must be greater than the 10-day MA. The events are also denoted by 1's for true and 0's for false. All of the elements must be there for a "Sell Recommendation"=all 0's.

3. Once you buy the stock you hold onto it until you have a sell recommendation. Only then can you Sell the stock. After you have sold you can not sell again until you Buy again.

The problem I am having is that once you buy the stock, the initial adjusted closing price that you bought at must stay the same for the entire column until you hit a sell recommendation. I am having trouble coming up with a nested IF function. This is assuming that a nested IF function is correct (incase anyone else has an idea of how this could be done). However, the function must be able to fill the entire column in one swoop.

Does anyone know how this is possible to hold a value until this other condition is met?

I have attached my spread sheet.

**masteff** · 06-24-2010, 05:24 PM

You need another column to indicate whether you're actually holding the stock (see attached, inserted column H). I then modified your Buy/Sell formulas to also look at col H. Then price is a simple IF statement.