------------------------------------------------------------------------------------------------------ I have only bought premium in a cash only account through I.B. for the last 20 years. I simply buy calls or puts and use a pre-determined Buy Limit, Sell Limit, & Stop. (Its just a supplemental retirement income.) With I.B.you can combine all 3 orders using a Bracket Order. You then press transmit and it does the rest. I believe one's risk per trade is the Buy Limit minus the Stop plus the Commissions. So if I buy 10 contracts with a -.50 Stop, I just lost -$500 plus -$16 commission. -$516 Risk on a $20K account is -2.6% total account risk per trade. That means I would have to lose 38 straight trades to be wiped out! I haven't lost more than 3 consecutive trades in 10 years. Some members on ET will argue that your stop plus commission isn't your total risk in a trade. They will say your total risk in a trade is everything you have into that trade, in other words your "total debit." Going on that premise I then have 10% to 15% of my total account into each trade. But I totally disagree with that premise. Its only possible to have a total wipe out on a trade with a stop if your holding the trade overnight and the market opens catastrophically (Black Swan Event) against you. That happens maybe once or twice per decade. So in that very rare occurance I would lose 10% to 15% of my account, still not a big deal. I just looked back a few minutes ago at all my 2017 trades to see what my worst surprise negative gap down opening price that took out my stop. There was only 1 trade that blew below my stop and cost me extra loss. Its was the difference between a normal -.50 stop and a negative next day gap down opening on the option of -.68. So a surprise occurance cost me an extra -.18 times 10 contracts (-$180), and that was it for 2017. That's why I define risk as stop minus entry price plus commisssions.
This is what I ended up doing before I saw userque's post. It is cumbersome and for some loop I wanted, difficult. The end result was I forced myself to learn VBA, but that has its own challenges and not exactly user friendly either. I have excel 2010, a Mac version so don't know if the circular function looping is there. If not I may have to upgrade to a newer version because from the link, it is what I needed. Thanks for your suggestions.
Circular reference is traditionally an error in Excel. I would rather use VBA or something else, than trying to use circular references outside of intended usage. This is an area where you want to move away from formulas (functions) and into more general purpose languages, because what you're trying to accomplish is to be free to do whatever you want to do, and a platform will restrict you through all its predefined structures and limitations.
"...At times, you may want to use circular references because they cause your functions to iterate—repeat until a specific numeric condition is met. ..." https://support.office.com/en-us/ar...483-bcf7-1b49cd50d123?ui=en-US&rs=en-US&ad=US
It states "you may want", but who here have actually used iterations like that and how was the experience? In my experience, circular references in Excel have always been a PITA, and was never designed for such usage, so will yield both problems and limitations due to that.
Right, of course. It's not telling you how you must code or build a sheet. My post was to show that using it was, in fact, an intended purpose. In response to your exactly opposite assertion. Yeah, I've never used them for anything. I did test them out long ago. Also, the OP asked a question, so I answered. He is aware of VBA and chooses a different path than you or I would. I wish him the best in solving his problem, whichever road he chooses. "What another eats, doesn't make me shit."
Its good to balance a discussion. However this use case was not designed in from the start, and not too useful. The feature can help to resolve badly formed spreadsheets but in too unpractical and surprising ways to rely on. So id not recommend using it other than just always having it turned on (better). Ive strong doubts this feature will provide new/useful functionality, but it was always best to keep on just to get to see results faster and for debugging purposes. IIRC it avoids full calculation of worksheets on every small change, which is its main purpose. I had to give up excel when files became > 200 mb. Its amazing what you can build with it, but anybody feeling the pain of VBA should get sound advice.
My cite was from Microsoft. They stated it can be used in the way the OP seeks. I have no information that MS didn't originally intend for it to be used that way and you've provided no reference. Even so, that's irrelevant as MS currently indicates that it can be used that way. Whether or not a feature of excel is useful or not is in the eye of the beholder. Again, the OP understands the power of VBA and the greater power of real languages yada yada yada. The OP asked a question about iterative calculations. I answered. I have no idea how useful the feature will be to the OP. I could guess--without knowing or seeing the OP's sheet; or knowing what the OP is trying to do--but that would be silly of me. The OP appears to be a grown man; If the OP asks for advice, rather that a specific question; I'll give him advice. VBA is an easy language. Anyone feeling pain with it (as far as being able to loop with it), will likely feel even more pain with real languages. Despite all the "...you should learn programing..." advice dished out on this website; everyone is not built for programming anything more than something simple.
Thanks for your coaching. I don't like VBA because I could not find functions within VBA to do my computation and has to circle back into Excel to get the right function, toggle between Excel and VBA is a pain. I am not smart enough nor have time to write my own function subroutines, not an excuse sir. Regards,