Results 1 to 10 of 13

Thread: Help with complex formula

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Help with complex formula

    Can anyone help with a solution to this problem, which I would like if at all possible to solve with a formula in a single cell. See attached example, which is just part of a large complex workbook. I have done a similar thing in another workbook by adding extra columns to do some of the calculations and then using a series of nested IF statements to make the final calculation, but in this situation there are reasons for not wanting to add extra columns.

    The formula that I am looking for in cell C10 is to calculate the true weighted average DM% of the closing stock of 65.880 tonnes from the recent deliveries. i.e. in this example it would be 27.500 @ 24.0%, 28.360 @ 24.5%, and 10.020 @ 42.0% = 26.95%. This sum needs calculating at the end of each month and the stock would be unlikely to exceed deliveries made in the last 7 days of the month (as shown), but as can be seen the DM% of deliveries varies quite widely, so it would be inaccurate to simply use the DM% of the last delivery (unless of course that accounts for the closing stock).

    Perhaps the answer is to do it via code rather than a formula as my attempts to write a complex formula with numerous nested IF statements are rather mind-boggling.

    Any help/comments would be much appreciated.
    Attached Files Attached Files

  2. #2
    I don't understand how you get to the answer you quote here.

  3. #3

    The material is a liquid co-product of variable dry matter (DM) content and the calculation required is to establish the DM% of the closing stock, which in the example shown is as follows:


    i.e. the closing stock is derived from the recent deliveries in reverse order - the 65.880 comprises the last two complete deliveries plus 10.020 from the delivery of 27.320 on 24th.

    In this case it is unlikely that the closing stock would comprise more than would have been delivered in the last 7 days, but if there is a method for doing this calculation for materials for which the last delivery might be further back that would be useful.

    Hope that helps to understand what is required and I look forward to any help you or anyone else can offer.

  4. #4
    Okay, try this ARRAY formula =SUM(B2:B9*C2:C9%)/SUM(B2:B9)

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    pignick, just in case you didn't know, you'll need to press CTRL+SHFT+Enter to commit the array formula.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Just noticed you call it percentage but don't hold it as such, so you might want this array fomula =SUM(B2:B9*C2:C9)/SUM(B2:B9)

Posting Permissions

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