Formula Help: Calculate Net Cost on Shares (excluding sell orders)

Show 40 post(s) from this thread on one page
Page 2 of 2 First 12
• Dec 7th, 2017, 10:23 AM
Flashbond
Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)
Quote:

Originally Posted by Marcelo Branco
I know, I know and unfortunately I also do not know how to provide the desired solution using Excel. If (big IF) the lots (Buy x Sale) are the same size *maybe* it's possible to create a formula (listen, i said maybe ;))

M.

Oh, yes... I think this is the stiuation. Otherwise it makes no sense. But still can't figure it out.

Let say there is no contribution of negative numbers to the grand sum. Their total effect is zero. But when? Since you can exclude their possitive effect, too...

What I mean is there should be a --() condition which will return zero on a nagative row but also look back and find the last positive accurance of the same value (there should be one at least! But which one?!?! Probably the last one :S) and provide an array like {0,0,0,0,1,1,1} to the SUMPRODUCT function.

I don't think such condition can be written.
• Dec 7th, 2017, 10:44 AM
Marcelo Branco
Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)
Maybe with a helper column to get the correspondent last price

 A B C D E 1 Qty Bought/Sold Price Bought/Sold Price Result 2 20600 0,24 0,24 1210 3 8000 0,38 0,38 4 -20600 0,4 0,24 5 -8000 0,35 0,38 6 8000 0,37 0,37 7 2000 0,28 0,28 8 2500 0,26 0,26 9 -8000 0,4 0,37

Formula in C2 copied down
=IF(A2>0,B2,LOOKUP(2,1/(A\$1:A1=-A2),B\$1:B1))

Formula in E2
=SUMPRODUCT(A2:A9,C2:C9)

Note that changing the ranges to the original data setup (ignoring the added last row of data)
=SUMPRODUCT(A2:A8,C2:C8)
we get 4170 as desired

M.
• Dec 7th, 2017, 02:00 PM
Flashbond
Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)
A tricky question: what happens if the data looks like below?

Qty Bought/Sold Price Bought/Sold
20,600 \$0.24
8,000 \$0.38
8,000 \$0.36
-20,600 \$0.40
-8,000 \$0.35
8,000 \$0.37
2,000 \$0.28
2,500 \$0.26

Should -8,000 on the 5th row be calculated with the 2nd row buying price or the 3rd row price?
• Dec 7th, 2017, 04:17 PM
Marcelo Branco
Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)
With such scenario my suggestion above fails:(.
Another helper column? Argh!! It seems it's not possible to create a formula to solve this problem...

M.
• Dec 7th, 2017, 08:34 PM
StephenCrump
Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)
With one helper column:

Qty: A2:A8
Price: B2:B8
C8: =MAX(0,A8)
C2: =C3+IF(A2>0,A2) Copy down to C7

To do it without a helper column, you could generate CumulativeBuys using a formula, but it would be long and messy.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: right;;">Qty Bought/Sold</td><td style="font-weight: bold;text-align: right;;">Price Bought/Sold</td><td style="font-weight: bold;text-align: right;;">Cumulative Buys</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">20,600</td><td style="text-align: right;;">\$0.24</td><td style="text-align: right;;">41,100 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">8,000</td><td style="text-align: right;;">\$0.38</td><td style="text-align: right;;">20,500 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">-20,600</td><td style="text-align: right;;">\$0.40</td><td style="text-align: right;;">12,500 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">-8,000</td><td style="text-align: right;;">\$0.35</td><td style="text-align: right;;">12,500 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">8,000</td><td style="text-align: right;;">\$0.37</td><td style="text-align: right;;">12,500 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2,000</td><td style="text-align: right;;">\$0.28</td><td style="text-align: right;;">4,500 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2,500</td><td style="text-align: right;;">\$0.26</td><td style="text-align: right;;">2,500 </td></tr><tr ><td style="color: #161120;text-align: center;">9</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;">10</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Result</td><td style="font-weight: bold;text-align: right;;">\$4,170.00</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
• Dec 12th, 2017, 06:57 PM
Tilly13
Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)
I think the conclusion is it really does need to be done manually ... I'll continue having a think about how to phrase this in formula-speak, but I believe it's close to impossible. Thanks all for trying to understand my nonsense and help out :)
• Dec 12th, 2017, 07:00 PM
Tilly13
Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)
Thanks Stephen, I will give this a try :) Definitely seems like what I'm looking for, despite my particularly poor description of the problem
• Dec 18th, 2017, 12:50 AM
Tilly13
Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)
Mate, this works perfectly. Thanks so much for understanding my nonsensical jumble and providing a solution that works! Cheers.
Show 40 post(s) from this thread on one page
Page 2 of 2 First 12