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

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

1. ## Re: Formula Help: Calculate Net Cost on Shares (excluding sell orders)

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.

2. ## 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.

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

4. ## 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.

5. ## 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.

Excel 2010
ABC
220,600\$0.2441,100
38,000\$0.3820,500
4-20,600\$0.4012,500
5-8,000\$0.3512,500
68,000\$0.3712,500
72,000\$0.284,500
82,500\$0.262,500
9
10Result\$4,170.00

Sheet1

6. ## 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

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

8. ## 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•