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

Thread: Help with complex formula

  1. #1

    Help with complex formula



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

    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
    Bob,

    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:

    ((27.500*24.0)+(28.360*24.5)+(10.02*42.0))/65.880

    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
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    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 (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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)

  7. #7
    Thanks Bob & Ken,

    That array formula is calculating the average DM of all the deliveries but is still not giving me the answer I'm looking for, i.e. the actual DM of the closing stock, which I tried to explain (but perhaps not clearly enough).

    As I suspected I don't think this can be done in a single cell without an additional column of preliminary sums to facilitate the final calculation.

    It's not an urgent matter and I'll continue to experiment with a few ideas unless someone else out there has any suggestions or experience of a similar situation.

  8. #8
    You said the answer is 26.95, that formula returns 26.95, so how can it be wrong?

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    Bob, on the example spreadsheet your formula yields 30.08%. The issue that pignick has here is that the used portion of the first item needs to be calculated.

    I added an intermediary column... puting the following in E2 and copying down to E9:
    =IF(B2<>"",IF($B$10-SUM(B2:B$9)>0,B2,$B$10-SUM(B2:B$9)+B2),0)

    I then adjusted the cell reference in your second array formula:
    =SUM(E2:E9*C2:C9%)/SUM(E2:E9)

    At that point I got to 26.95
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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.

  10. #10
    That is so Ken, but on the spreadsheet B2 is 27.320 and C2 is 42.00, whereas the worked example gave 10.02 and 42. Change the 27,320 to 10.02 and my formula gives 26.95 as described.

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
  •