Question for experienced Excel users...

C

CowboyBlue

Ok i have a tracking sheet on excel designed to track my positions/pnl, i am having troubles with a particular formula.

I have it set up on an "IF" statement where if the pnl of a stock is less than -400 it locks in at -400 and wont go any lower, based on me setting up a stopout for -400 on any given position. My problem i am having is i cant lock it at -400 if it goes to -300 then it ends up -300, i just want it to lock the second it hits -400, is there a way to do this?
 
Quote from dwl603:

My problem i am having is i cant lock it at -400 if it goes to -300 then it ends up -300, i just want it to lock the second it hits -400, is there a way to do this?
More...
To be specific, that just doesn't make any sense, the way you are describing it.
 
If you're updating cells with live information, I think you're going to have to do what you wish with VBA/macro. I can't think of a way to do it with just formulas.

If you use this:
Private Sub Worksheet_Calculate()

your VBA code will run every time the worksheet calculates. Make it check the value of the cells you have PL in and if any are -400 it will have to replace the IF with the value. This isn't a great solution but the only one I can think of.
 
Assume your data is in cell A, and
tabulated in B.

just use formula for cell B:
=if(cell(A) <-400,-400,cell(A))

if you want the original cell balance to carry down,
=if(cell(A)<-400,cell(B(n-1)),cell(A))

Unless I"m missing something, this is a trivial problem and does not require VBA.

If it doesn't do what you want. Post a small example of some data and what you would like for it to do.
 
Quote from monstimal:

If you're updating cells with live information, I think you're going to have to do what you wish with VBA/macro. I can't think of a way to do it with just formulas.

If you use this:
Private Sub Worksheet_Calculate()

your VBA code will run every time the worksheet calculates. Make it check the value of the cells you have PL in and if any are -400 it will have to replace the IF with the value. This isn't a great solution but the only one I can think of.
More...
After reviewing the problem in excel this is the best answer I could come up with also.

You need a VBA script that can either:

a) convert the formula to a value when it hits the -400 mark, or
b) have another portion of the spreadsheet reference the cell, and have a VBA script convert that formula to a value when the origial (source cell) hits the -400 mark.

Link
 
Quote from dtrader98:

Assume your data is in cell A, and
tabulated in B.

just use formula for cell B:
=if(cell(A) <-400,-400,cell(A))

if you want the original cell balance to carry down,
=if(cell(A)<-400,cell(B(n-1)),cell(A))

Unless I"m missing something, this is a trivial problem and does not require VBA.

If it doesn't do what you want. Post a small example of some data and what you would like for it to do.
More...

ok here is the formula i have

=IF(J12<=-400,-420,J12)

so basically it means that this cell should equal j12, unless j12 is less than or equal to -400 in which case it equal -420 (accounting for slippage), if it is not less than or equal to -400 it remains J12.

What i am trying to do is if it goes over -400 i want it to lock there instead of coming back to -300 if it dips back below -400. Given the equation i gave you could you give me the euqation i would need to lock it if it goes over -400, this is supposed to respresent a stopout, so if it hits -400 it is supposed to lock in that price.

So if it is -400 it will equal -420 but if not it equals J12 the pnl column, the problem is if the stock comes back from there it will continue to go higher just never lower thasn -400, i need it to lock once it hits that number.
 
Again, it would be much simper to
simply post a small table or column
of actual values that you want processed. Your descriptions are a bit ambiguous. It would help to show where J12 itself comes from. If I understand you correctly,
you could do something in j12 like:

=If(J11=-420,-420,if(j12<=400,-420,j12))

this will lock and hold -420 once it is
triggered, no matter what other value
comes into play.



Quote from dwl603:

ok here is the formula i have

=IF(J12<=-400,-420,J12)

so basically it means that this cell should equal j12, unless j12 is less than or equal to -400 in which case it equal -420 (accounting for slippage), if it is not less than or equal to -400 it remains J12.

What i am trying to do is if it goes over -400 i want it to lock there instead of coming back to -300 if it dips back below -400. Given the equation i gave you could you give me the euqation i would need to lock it if it goes over -400, this is supposed to respresent a stopout, so if it hits -400 it is supposed to lock in that price.

So if it is -400 it will equal -420 but if not it equals J12 the pnl column, the problem is if the stock comes back from there it will continue to go higher just never lower thasn -400, i need it to lock once it hits that number.
More...
 
ok now you se where J12 is, you see how it is -504? so
k12 = negative 420 meaning i was stopped out, now the problem is if J12 goes higher than -400, K12 will revert back to being J12, i want K 12 to lock once J12 goes over -400 to represent a stopout.

the equation i have for K12 is =IF(J12<=-400,-420,J12)

the problem is if J12 goes back under negative 400 then k12 will also go back under negative 400 and i need k12 to lock once J12 goes -400 or more.....

attachment.php
 

Attachments

You must log in or register to reply here.
Back
Top