OK, Profit Loss without using a minus in formula

[Brad1m]

Guys, Ive got a new problem "Nothing new there right...!"

I'm trying to set up a profit loss formula in a column thats 800 deep.

So break even and profit is around row 100, the problem is I cant use a minus sign - in the formula or it all goes pear shaped when it gets to profit.

I have turned formulas inside out and upside down all day trying to come up with something that works but still no love...

It would be easy to do if I could simply use $D$8-A10 through to $D$8-A800 but this is obviously going to fall apart when it gets to profit zone.

Anyone got any ideas??? Is there a function I need to use?

 

[Brad1m]

http://i1130.photobucket.com/albums/m525/Brad1m/Captureoptcalcother.jpg

Thats what it's supposed to look like

 

[Brad1m]

Here's what it's supposed to look like

http://i1130.photobucket.com/albums/m525/Brad1m/Captureoptcalcother.jpg

 

[dk]

Hi mate

It's still hard to see what happening because you haven't output the formulas when you've used the HTMLMaker. Can you do it again, making sure that you choose the Generate HTML (Specify Option), All Formulas. That way we can see what formulas you're currently using.

Thanks
DK

 

[dk]

BTW, for some reason I can't see the photobucket images that you've linked to (may be that it's blocked because I'm at work). Either way, we usually prefer to not link to external images if possible and keep everything "on the board"

 

[Brad1m]

Thanks dk

I have told it to use all formulas this time

Should have deleted all column C because it's just wrong...

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #FFFF00;background-color: #000000;;">My Sports Options Calculator </td><td style="text-align: center;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;color: #FFFF00;background-color: #000000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-bottom: 1px solid black;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;border-bottom: 1px solid black;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;border-bottom: 1px solid black;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;border-bottom: 1px solid black;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;border-bottom: 1px solid black;color: #FFFF00;background-color: #000000;;"></td><td style="text-align: center;border-bottom: 1px solid black;color: #FFFF00;background-color: #000000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00B0F0;;">Cash available for this trade</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #00B0F0;background-color: #00B0F0;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;color: #00B0F0;background-color: #00B0F0;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">$20,000 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF00;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Depth Averager (in cents)</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #66FFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #0070C0;;">Mid Price</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #00B0F0;;">Fast Trade Price</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #00B0F0;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #00B0F0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Enter Bid Price</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">64</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #FF3300;background-color: #BFBFBF;;">68</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00CC00;;">66</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Fast Trade Sell</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Enter Ask Price</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">72</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FF3300;background-color: #BFBFBF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFC000;;">70</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Fast Trade Buy</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Price Paid</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Contract Size</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Contracts</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Actual Investment </td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #66FFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Trader Dealer Fees</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">1000</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFF00;background-color: #000000;;">400</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$20,000 </td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Option Price</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Trade Value</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Gross P/L</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Net P/L</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;">Net %</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #66FFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;color: #00B050;;">$0.000</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;color: #00B050;;">$0.00</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;color: #00B050;;">$0.005</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$2,000.00</td><td style="text-align: right;;">$40,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;color: #00B050;;">$0.010</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$4,000.00</td><td style="text-align: right;;">$19,523.80</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;color: #00B050;;">$0.015</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$6,000.00</td><td style="text-align: right;;">$19,285.71</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;color: #00B050;;">$0.020</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$8,000.00</td><td style="text-align: right;;">$19,047.62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;color: #00B050;;">$0.025</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$10,000.00</td><td style="text-align: right;;">$18,809.52</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;color: #00B050;;">$0.030</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$12,000.00</td><td style="text-align: right;;">$18,571.43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;color: #00B050;;">$0.035</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$14,000.00</td><td style="text-align: right;;">$18,333.32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;color: #00B050;;">$0.040</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$16,000.00</td><td style="text-align: right;;">$18,095.24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;color: #00B050;;">$0.045</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$18,000.00</td><td style="text-align: right;;">$38,000.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;color: #00B050;;">$0.050</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$20,000.00</td><td style="text-align: right;;">$50.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;color: #00B050;;">$0.055</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$22,000.00</td><td style="text-align: right;;">$17,380.94</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;color: #00B050;;">$0.060</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$24,000.00</td><td style="text-align: right;;">$17,142.84</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;color: #00B050;;">$0.065</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$26,000.00</td><td style="text-align: right;;">$16,904.76</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;color: #00B050;;">$0.070</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$28,000.00</td><td style="text-align: right;;">$16,666.67</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;color: #00B050;;">$0.075</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$30,000.00</td><td style="text-align: right;;">$16,428.56</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;color: #00B050;;">$0.080</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$32,000.00</td><td style="text-align: right;;">$16,190.46</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;color: #00B050;;">$0.085</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$34,000.00</td><td style="text-align: right;;">$15,952.38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;color: #00B050;;">$0.090</td><td style="font-weight: bold;text-align: center;color: #00B050;;">$36,000.00</td><td style="text-align: right;;">$15,714.28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=SUM(<font color="Blue">B6,B5</font>)*0.5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=SUM(<font color="Blue">(<font color="Red">B6-B5</font>)*0.26,B5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=SUM(<font color="Blue">(<font color="Red">B6-B5</font>)*0.74,B5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">=ROUNDDOWN(<font color="Blue">D3/(<font color="Red">A8*10</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=C8*10*A8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=A10*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B11</th><td style="text-align:left">=A11*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B12</th><td style="text-align:left">=A12*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B13</th><td style="text-align:left">=A13*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B14</th><td style="text-align:left">=A14*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B15</th><td style="text-align:left">=A15*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B16</th><td style="text-align:left">=A16*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B17</th><td style="text-align:left">=A17*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B18</th><td style="text-align:left">=A18*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B19</th><td style="text-align:left">=A19*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B20</th><td style="text-align:left">=A20*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B21</th><td style="text-align:left">=A21*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B22</th><td style="text-align:left">=A22*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B23</th><td style="text-align:left">=A23*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B24</th><td style="text-align:left">=A24*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B25</th><td style="text-align:left">=A25*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B26</th><td style="text-align:left">=A26*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B27</th><td style="text-align:left">=A27*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B28</th><td style="text-align:left">=A28*1000*C$8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">=SUM(<font color="Blue">C8*0.5</font>)/A11</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C12</th><td style="text-align:left">=0.97619*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C13</th><td style="text-align:left">=0.9642857*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C14</th><td style="text-align:left">=0.9523809*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C15</th><td style="text-align:left">=0.940476*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C16</th><td style="text-align:left">=0.9285714*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C17</th><td style="text-align:left">=0.916666*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C18</th><td style="text-align:left">=0.9047619*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C19</th><td style="text-align:left">=D8--B19</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C20</th><td style="text-align:left">=A20*B8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C21</th><td style="text-align:left">=0.869047*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C22</th><td style="text-align:left">=0.857142*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C23</th><td style="text-align:left">=0.845238*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C24</th><td style="text-align:left">=0.8333333*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C25</th><td style="text-align:left">=0.821428*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C26</th><td style="text-align:left">=0.809523*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C27</th><td style="text-align:left">=0.797619*D8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C28</th><td style="text-align:left">=0.785714*D8</td></tr></tbody></table></td></tr></table><br />

 

[dk]

OK, we're making progress! Can you describe how the gross profit/loss should be calculated? I noticed that in your current (incorrect) formulas in column C you have lots of hardcoded values such as 0.97619, 0.9642857, and so on - what's the significance of them?

BTW, I have only a very basic knowledge of options hence my questions.

 

[Brad1m]

dk. Think I have had the moment of epiphany...

The hard coded stuff was me being a moron... trying to work out where the % disappeared too... I was looking at .005 as .995 short of 100 but it's not...

.005 in this case is actually .1 because the buy price is 20% of 100

Think I have solved the rubix cube... lol

I will go mess with it and let you know how I go.

PS. Thanks for your help mate, I know you would have figured it out long ago if I knew how to post stuff on the board properly.

 

[dk]

OK, well glad you have had the epiphany! Just post back here if you get stuck and I'll try and help.

Cheers
DK

 

[Brad1m]

dk heres the formula I came up with for column C "Theres probably a better one, but this works"

=SUM((100/$A$8*A11)-1)*$D$8

I knew the answer would be a simple one, can't believe I spent like 8hrs with calc in hand twisting turning manipulating numbers until my head felt like it was gunna cave in.

.005 is not .995 DUH!!! Cant believe I missed that for so long.