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.
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.