Stock Loss "Cap" Formula

[StockPrice]

I’m trying to create formula that will calculate the loss on a stock given certain conditions. I need to “cap” the loss based on the average price of the stock over the 90 day period after it drops. The cap will either eliminate the loss or lower it, depending on what the 90 day average price is.

An example:
The stock falls from $50 to $5.
If the 90 day average price is below $5, then the cap is moot, and the loss is (50-5=45)
If the 90 day average price is above $50, then the cap eliminates the loss (i.e. loss is 0).
If the 90 day average price is between $5 and $50, say it’s $35, then the cap partially reduces the loss and I get (35-5=30).

I have all the inputs, I’m just having trouble coming up with a formula which will return the correct answer under every condition listed above. I think it can be done using IF, AND, and possibly NOT statements, but I just can’t get it.

Thanks!

 

[etaf]

=if( cell with 90 day average - or the calculation < the cell with the final price (in your example 50 to 5) , 90 day average - cell with final price,
IF (

How do we know the value is above the example $50 ,what is the cell with $50 ?

can you layout the data

you have a cell with
stock price say each day and its dropping
how will the rules work

90day average is OK
latest stock price in example $5

The stock falls from $50 to $5.

$50 - is this a drop from when the stock was first entered $50 on the spreadsheet ?

 

[anwaee2]

Assuming you have information in three different columns:
Column "A" (or your column) = purchased price, start price, price at beginning of period etc.
Column "B" (or your column) = price, price now, price at end of period, etc.
Column "C" (or your column) = 90 day average
Column "D" (or your column) = Loss or ???

Try this for column "D" or your column: =IF(OR(A4="",B4="",C4=""),"",IF(C4<B4,A4-B4,IF(C4>A4,0,C4-B4)))