Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Weighted Average with uneven split

  1. #1

    Weighted Average with uneven split



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a data which relates to particular vendor where the payment is made in single line , but we have to review the Invoice and give the break up(Manually) for each line and later the single line will be splitted on weighted average method based on the breakup, which makes a long time work is there any formula or Macro that would help me to do the job very easily

    I have also attached sample file for your reference


    Below is the example of the Issues, Solution is appreciated
    Question

    Vendor name AMT
    chevron 5000
    Glencore 3000
    TCS 5000
    G&F 40000



    Answer

    Vendor name AMT Breakup Weighted average Formula
    chevron 5000 1000 1000 =C14/SUM($C$14:$C$17)*B14
    2000 2000
    1000 1000
    1000 1000
    Glencore 3000 1500 1500
    1500 1500
    TCS 5000 1500 326.09
    1500 326.09
    1000 2,17.91
    1000 2,17.91
    G&F 40000 2000 3636.64
    2000 3636.36Krish.xlsxKrish.xlsx

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    It's complicated but here goes :-)

    In E15 create the ARRAY formula (press Cntrl Shift Enter) when done
    E15 = IF(B15="",E14,B15/SUM(OFFSET(C15,0,0,MATCH(FALSE,ISBLANK(B16:$B1000),0),1)))

    In D15 the formula is simply
    D15 = C15*E15

    Copy both formula down. At the end (currently Line 27) you need anything (eg B27= 1) to fix the final item

    Going thru the formula in steps
    1: IF(B15="",E14, <do something>)
    If it is blank get the amount / sum from the previous cell otherwise <do something>

    2:B15/SUM( <calculate amount/sum for that invoice>

    3:OFFSET(C15,0,0,<How many blank rows>,1)))
    using the offset to sum the number of rows

    4: MATCH(FALSE,<Array to test>,0)
    find which row is the first NON blank row

    5: ISBLANK(B16:$B1000)
    Makes an array of True/False

    I have assumed 1000 rows, can make that to the end of the sheet or change $B1000 to B100 (assuming you won't have more than 100 items per invoice)
    Last edited by WizzardOfOz; 2014-09-17 at 06:40 AM.

  3. #3
    If you can put some string in A31, such as end, to signify the end of the table, this single array formula will do it

    =IF($C15="","",INDEX($B$1:$B$31,MAX(IF($A$15:$A15<>"",ROW($A$15:$A15))))*$C15/SUM(INDEX($C$1:$C$31,MAX(IF($A$15:$A15<>"",ROW($A$15:$A15)))):INDEX($C$1:$C$31,MIN(IF(A16:$A$31<>"",ROW($A16:$A$31)))-1)))

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    What??? That went whizzing past overhead.

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    sriram170,

    Please do not crosspost to other forums without first providing links to those threads. It is unfair to all the volunteers who give their free time to answer questions.

    Read: http://www.excelguru.ca/content.php?184


  6. #6
    Am sorry, this is because i can get different answers in different methods... will take care of it in future

  7. #7
    Thank you very much WizzardOfOz and Bob Phillips
    Last edited by sriram170; 2014-09-18 at 06:14 PM.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by sriram170 View Post
    Am sorry, this is because i can get different answers in different methods... will take care of it in future
    yes, that is fine, but have the courtesy to post those links where others can find the threads and understand if you have gotten an answer before they waste their time.


  9. #9
    Quote Originally Posted by NBVC View Post
    yes, that is fine, but have the courtesy to post those links where others can find the threads and understand if you have gotten an answer before they waste their time.

    Sure and thanks and please close the thread

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Are you going to post the link?

    Are you going to go back to those other threads and tell people that you have a solution, so that they don't continue to waste time?


Page 1 of 2 1 2 LastLast

Posting Permissions

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