+ Reply to Thread
Results 1 to 14 of 14

Running balance weighted average formula?

  1. #1
    Registered User
    Join Date
    05-21-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Running balance weighted average formula?

    Hey,

    Does anyone know a formula to use for the following?

    excel2.jpg

    Rows are either 'a' or 'b' classifications and column F shows the running balance for each. I want to multiply the running balance by the weighted average rate. Example is cell H4 should be (200,000/2,200,000*7%)+(2,000,000/2,200,000*12%). Is there a formula for this? I also only want to include the current row and all rows above in the calculation. The calculation should not include any rows below, therefore the rows are also classified by numbers in column A. I was thinking a SUMPRODUCT function may work so I can multiply column E times column D but I would need to divide each product by the amount in column F only in the current row. I would also set some sort of IF function to that column A is less than or equal to the current value for column A AND column B = either a or b.

    Pretty complex formula I think. DOES IT EXIST???

    Thanks in advance for help.
    Last edited by rb473363; 05-22-2012 at 09:23 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex? Formula

    it would help if we had your workbook, instead of a picture to work with
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-21-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Complex? Formula

    Agreed. I dont see anywhere to post attachments though - just pictures.

    Good possibility I might be blind though.

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Complex? Formula

    Click on Forum Rules @ top of page. Scroll down to "Want to get your question answered quickly?" area (follows rule 13) - instructions are there.

  5. #5
    Registered User
    Join Date
    05-21-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Complex? Formula

    hey here's the workbook - thanks
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-21-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Running balance weighted average formula?

    Bumping post

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Running balance weighted average formula?

    Perhaps in G2 and copy down,

    =SUMPRODUCT((B$2:B2=B2) * D$2:D2 * F$2:F2) / SUMIF(B$2:B2, B2, F$2:F2)
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    05-21-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Running balance weighted average formula?

    Hey thanks shg - on the right track but I'm not looking to divide by the sum of all 'a' and 'b' amounts in column F. I am just looking to divide by the one amount in column F in the same row as the formula is. For example, cell G4 should divide by 2,200,000 - not 4,200,000. However, I don't just want the sum of all products to divide by 2,200,00. I want each product to divide by that amount THEN take the sum of all of those amounts. Cell G4 should equal approximately 11.55% (=(E2/F4*D2)+(E4/F4*D4)). Also Cell G6 should equal 7.25% (=(E2/F6*D2)+(E4/F6*D4)+(E6/F6*D6)).

  9. #9
    Registered User
    Join Date
    05-21-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Running balance weighted average formula?

    Does a formula even exist for something complex like this?

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Running balance weighted average formula?

    I don't understand what you're trying to calculate.

  11. #11
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Running balance weighted average formula?

    I'm guessing here based on post #8

    =SUM(E$2/F2:F17*D$2,E2:E17/F2:F17*D2:D17) array formula (confirmed by Ctrl+Shift+Enter)
    the result is 1.16

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Running balance weighted average formula?

    I think there's supposed to be a conditional in there someplace, Cutter.

  13. #13
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Running balance weighted average formula?

    I took a shot.
    What I don't get is what's in H4. "Is there a formula for this?" immediately following the formula for that (and repeated in post 8).

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Running balance weighted average formula?

    And a fine shot it was ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1