Formula Help: Calculate Net Cost on Shares (excluding sell orders) - Page 2
Formula Help: Calculate Net Cost on Shares (excluding sell orders)
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

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

  1. #11
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    603
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

     
    Quote Originally Posted by Marcelo Branco View Post
    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.
    Last edited by Flashbond; Dec 7th, 2017 at 09:26 AM.

  2. #12
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,271
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default 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. #13
    Board Regular Flashbond's Avatar
    Join Date
    Mar 2010
    Location
    Turkey
    Posts
    603
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default 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?
    Last edited by Flashbond; Dec 7th, 2017 at 01:02 PM.

  4. #14
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,271
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default 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. #15
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,268
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    With one helper column:

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

    C10: =SUMPRODUCT(--(ROW(Qty)>=MAX(IF(CumulativeBuys>=SUM(Qty),ROW(Qty)))),--(Qty>0),Qty-IF(CumulativeBuys>SUM(Qty),CumulativeBuys-SUM(Qty)),Price)

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

    Excel 2010
    ABC
    1Qty Bought/SoldPrice Bought/SoldCumulative Buys
    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



    Last edited by StephenCrump; Dec 7th, 2017 at 07:35 PM.

  6. #16
    New Member
    Join Date
    Dec 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #17
    New Member
    Join Date
    Dec 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #18
    New Member
    Join Date
    Dec 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com