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?
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.
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
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.
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.....