Help with complex formula

pignick

New member
Joined
Apr 26, 2011
Messages
5
Reaction score
0
Points
0
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.
 

Attachments

  • Example.xlsx
    10.6 KB · Views: 20

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I don't understand how you get to the answer you quote here.
 

pignick

New member
Joined
Apr 26, 2011
Messages
5
Reaction score
0
Points
0
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Okay, try this ARRAY formula =SUM(B2:B9*C2:C9%)/SUM(B2:B9)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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. ;)
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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)
 

pignick

New member
Joined
Apr 26, 2011
Messages
5
Reaction score
0
Points
0
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You said the answer is 26.95, that formula returns 26.95, so how can it be wrong?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
True enough. I read the question as needing that part implied so it would pick it up automatically though. ;)
 

pignick

New member
Joined
Apr 26, 2011
Messages
5
Reaction score
0
Points
0
Thanks Ken,

As I suspected at the outset it clearly can't be done without that extra column of preliminary calculations, but at least I now have a better grasp of using an array formula.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
...it clearly can't be done without that extra column of preliminary calculations...

Oh, I don't know about that. Every time I say something can't be done, someone comes along and does it. There are some real masters of formulas out there who can do some pretty cool things. I just find it easier with the helper column.
 
Top