Results 1 to 10 of 13

Thread: Help with complex formula

Threaded 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

Posting Permissions

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