How To "Anchor" a Value If It Starts To Decrease

arizonawill · May 18th, 2002, 05:20 PM

I have a worksheet whereby I enter current stock price (SP). I have a stop-loss-sell-price (SLSP) which is 90% of the current stock price. As long as the SP rises, the SLSP will also rise. That is OK. What I want is a way to keep the highest value obtained by the SLSP when/if the SP starts down and goes below the highest recorded SLSP. When this happens, I would like to flag it to SELL. In other words, let the SLSP rise as the SP rises but forbid the SLSP from coming down as the SP comes down.

Jay Petrulis · May 18th, 2002, 09:17 PM

Hi,

Suppose your stock price data starts in cell A2 and continues down column A.

In B2, type the following:
=A2*0.9

In B3, type the following:
=IF(A3
Please note the mixed absolute/relative referencing. It is important.

Copy this down your list.

The following event macro will automatically add the formula as entries are added.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 And Target.Column = 1 And Target.Row >= 2 Then
    Range("B" & Target.Row) = _
    "=IF(RC[-1]

HTH,
Jay

RET79 · May 19th, 2002, 06:29 AM

Jay. I'm impressed, so you can get an event macro to enter formulas automatically??

I like the sound of this...

RET79

May 18th, 2002, 09:17 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi, Suppose your stock price data starts in cell A2 and continues down column A. In B2, type the following: =A2*0.9 In B3, type the following: =IF(A3 Please note the mixed absolute/relative referencing. It is important. Copy this down your list. The following event macro will automatically add the formula as entries are added. Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 And Target.Column = 1 And Target.Row >= 2 Then Range("B" & Target.Row) = _ "=IF(RC[-1] HTH, Jay

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 18th, 2002, 05:20 PM	#1
arizonawill New Member Join Date: May 2002 Posts: 1	I have a worksheet whereby I enter current stock price (SP). I have a stop-loss-sell-price (SLSP) which is 90% of the current stock price. As long as the SP rises, the SLSP will also rise. That is OK. What I want is a way to keep the highest value obtained by the SLSP when/if the SP starts down and goes below the highest recorded SLSP. When this happens, I would like to flag it to SELL. In other words, let the SLSP rise as the SP rises but forbid the SLSP from coming down as the SP comes down.

May 19th, 2002, 06:29 AM	#3
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Jay. I'm impressed, so you can get an event macro to enter formulas automatically?? I like the sound of this... RET79