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?

 

[dk]

hi,

It's not entirely clear what you're after...are these variance formulas that you're talking about? What is in column A and what is in column D? Why does it go "pear shaped when it gets to row 100"? Can you show an example of your data and where the problem is and you should then get some help....

DK

 

[Brad1m]

DK I'm not sure how I show an example? It's asking for a URL and Im not sure how to get it there?

A8 has number 42 "price of options" but this will change constantly
C8 has 11 "the amount of contracts" that can be purchased according to budget or D3 (Which is $5000 for this excercise) D8 is then populated from these giving a total option only "not brokerage" cost

These 3 cells work together with D3 being the one everything is calculated from.

The profit loss column (A10:A800) is neutral at 42 at this time but this is dynamic
so 42 (Cell A52) will only be $0 until next option with different price is purchased and it works in 0.005 increments profit to where ever is needed and back from 42 (Which is Cell A52) in 0.005 increments to total loss which is what ever $ value is in Cell D8

The columns all start at Row 10 so total loss is A10 and massive profit is A800

Phew man... if you can understand all that your a freak'n genius dude...

How do I post it to a URL??? lol

 

[dk]

You can show your sheet on the board by downloading the HTML Maker from here: http://cid-8cffdec0ce27e813.office.live.com/browse.aspx/MrExcel

Although your description makes some sense I still don't know what you're after....

 

[Brad1m]

OK I have done all that but when I click Generate HTML nothing seems to happen.

If you like you can send me an email? And I will post it to whoever wants to help


tagalonghc@gmail.com

 

[dk]

Yes, it would be better if it displayed a message box to tell you that it's done something but it doesn't. Once you click Generate HTML then come onto here and paste - it should paste a load of HTML which should then show your data. There is a test forum available if you want to test first....

 

[Brad1m]

Hey Dudes..... HELP!

I cant make the HTML Maker work but I am leaving my email address here for anyone that would like use dropbox to take a look and HOPEFULLY Help my dumb-r's...

I use this email for these purposes, and will not share, sell or give any of your details to anyone EVER.



My email: tagalonghc@gmail.com

Just shoot me an email and I will be able to share this spreadsheet and hopefully get some insight.

 

[Brad1m]

Ok here goes
<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;;">4</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;;">500</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,500.00</td><td style="text-align: right;;">$19,761.90</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;;">$5,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;;">$7,500.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;;">$10,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;;">$12,500.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;;">$15,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;;">$17,500.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;;">$20,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;;">$22,500.00</td><td style="text-align: right;;">$17,857.14</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;;">$25,000.00</td><td style="text-align: right;;">$17,619.04</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;;">$27,500.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;;">$30,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;;">$32,500.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;;">$35,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;;">$37,500.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;;">$40,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;;">$42,500.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></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 />

 

[Brad1m]

Those profit loss figures are WRONG! lol

 

[Brad1m]

Still haven't figured out how to do this????

You can see the price of the option in the picture is 4 and we get 500 contracts for our 20,000
In Cell A18 $0.040 is where we are at all prices lower than $.040 in column A will be loss and all above will profit.

"I think???" I Can't simply subtract column B from C8 because when you get into profit it wont work.

Everything needs to be dynamic "is that the right word?" so... no matter what values are in A8 B8 C8 D8 D3 all columns below simply do the work.

Rows 5 and 6 are separate from these formulas

It's Doing My Head In... I'm sure it will be an Epiphany Moment when I finally get it or someone points me in the right direction.