• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating a moving weighted average during Resource Scheduling

Hui

Hui

Excel Ninja
Staff member
I have a small query that you maybe able to help me with.

It involves scheduling resource usage similar to my post:
https://chandoo.org/wp/2010/11/18/scheduling-variable-sources/

I use a similar technique shown in the attached file:
upload_2018-4-18_23-31-27-png.51561


I have a list of resources (left) which are to be used in order top to bottom
They are to be used as per the production profile at the top Row 6 (Red) (left to right)
I want to calculate the weighted average grade (%Fe) Row 36 (Yellow)

I currently use a large block of helper cells to schedule the tonnages
and then use a sumproduct formula to derive the (Scheduled Tonnages x Grade) / Schedule Tonnages (Row 36)

I am after a formula which will do the grade directly from the Required schedule without the use of the helper cells (The large block of 0's and stepped data in the middle)

ie:
upload_2018-4-20_8-21-49.png


Sample file attached

Look forward to any and all assistance
 

Attachments

Last edited:

I was thinking about a double click filling all values at once …

So without helper columns, I can't see any formula
but it could be a challenge for a formula master !
​
 
I have seen a formula twice in my life that does this
Once was in 1997 and I had a copy of the file on a CD that became unreadable
Second time was here. I saw a comment to a question here about 7 years ago, But alas I can't find it...
 

As I can see the VBA calculation via INDEX & SUMPRODUCT,
yes it's really a true challenge for Narayank, Luke, Bosco
or any other Excel formula master !​
 
@Hui
Would any of these formulas do it for you?
There is quite a lot of processing of zeros but I suspect that the effort required to build a sparse array would be greater than any savings achieved by not needing to grind through zeros.
Peter
 

Attachments

Peter

Thanx for those formula

They all work, except that I am trying to get away without the helper cells on which they all rely

Ultimately I want a solution that look like:
upload_2018-4-20_8-14-25.png

Without using the block of helper cells
 
Marc L said:
As I can see the VBA calculation via INDEX & SUMPRODUCT,
yes it's really a true challenge for Narayank, Luke, Bosco
or any other Excel formula master !​
Click to expand...
Hi Marc ,

Sorry , but I wouldn't call myself an Excel formula master.

In my opinion , the first Excel formula master on this forum was Sajan , followed by Haseeb A , and Jeffrey Weir.

All 3 are not active on this forum any longer , though Jeff posts once in a while.

At present , on this forum , the only true Excel formula masters I can think of are @Bosco , @John Jairo V and @Lori.

Hopefully , this tagging may result in their looking into this problem.

Narayan
 

Don't be so modest (maybe too shy) but to my eyes
as to those of many others you are one …

Here in this case I can't imagine how a direct formula can reach the result.
But it would be a pleasure to see one achieving this …
​
 
Ah! In that case you are going to like this less, not more.

Of course, since they are all array formulas, the calculation may be performed on any worksheet which could be hidden.

The additional tables are required because I have organised the calculations as accumulations rather than using nested summations. If I revert to nested partial summations, I could remove the additional ranges (at the minor expense of requiring about 6x the number of arithmetic operations). The catch is that the index operations required to identify the partial sums may prevent further aggregation.
 

Attachments

There is a single cell solution as I have seen it done twice
But haven't kept a copy... Grrrr

Sorry, Peter, that's heading in the wrong direction
 
Hi, to all!

It's a pretty interesting problem, and for a single formula, it's a big challenge.

Initially, I managed to calculate the first value, maybe it helps with something:

_r : $C$10:$C$31
_r1 : $C$9:$C$30

Array Formula:

=SUM(IFERROR(EXP(LN(IF(F$6-MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1))<_r,F$6-MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1)),_r))),)*$D10:$D31)/F$6

When I have more time, I give it a deeper look. Blessings!
 
Interesting problem. Can't think of formula solution off top of my head...
I tend to use code or PQ in conjunction with DAX for this sort of thing, if I were to do it in Excel.
 
John Jairo V said:
Hi, to all!

It's a pretty interesting problem, and for a single formula, it's a big challenge.

Initially, I managed to calculate the first value, maybe it helps with something:

_r : $C$10:$C$31
_r1 : $C$9:$C$30

Array Formula:

=SUM(IFERROR(EXP(LN(IF(F$6-MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1))<_r,F$6-MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1)),_r))),)*$D10:$D31)/F$6

When I have more time, I give it a deeper look. Blessings!
Click to expand...
I quietly admire this formula, blink and go numb after a while. o_O
 
John Jairo V said:
Hi, to all!

It's a pretty interesting problem, and for a single formula, it's a big challenge.

Initially, I managed to calculate the first value, maybe it helps with something:

_r : $C$10:$C$31
_r1 : $C$9:$C$30

Array Formula:

=SUM(IFERROR(EXP(LN(IF(F$6-MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1))<_r,F$6-MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1)),_r))),)*$D10:$D31)/F$6

When I have more time, I give it a deeper look. Blessings!
Click to expand...

John

You are a champion!

It is a truly remarkable formula to which you should be proud ! ! !

I can and have extended your formula to do the cumulative cells

F36: Your formula above

G36: =(SUM(IFERROR(EXP(LN(IF(SUM($F$6:G$6)-MMULT(N(ROW(_r1)>= TRANSPOSE(ROW(_r1))),N(+_r1))<_r,SUM($F$6:G$6)-MMULT( N( ROW(_r1) >= TRANSPOSE(ROW(_r1))), N(+_r1)),_r))),) * $D10:$D31) - SUMPRODUCT($F$6:F6,$F$36:F36)) / G$6 Ctrl+Shift+Enter
Copy G36 across
 
Last edited:
Peter Bartholomew said:
Maybe this is moving in the right direction.
So far just one period with 5 parcels.
Click to expand...

Peter

Thankyou for your efforts

Although this is an interesting approach and interesting in its own right, you may have misread the question.

I am not trying to achieve a target grade value by mixing sources.
I simply want to report what grade is achieved by delivering variable parcel sizes & grades into a variable production schedule.
That is each period will have differing quantities from the parcels available to it. Parcels are consumed in a FIFO basis. One a pacel is used it is gone and the next parcel is consumed.
 
For anybody following this along
Using John's modified formula achieved this:
upload_2018-4-21_11-36-32.png
Note that all values are correct except the last grade
It is wrong as it has divided by the 40,000tonnes, but there is only 34,292t available.

So a simple redesign of the scheduling row, Row 6, so as not to over schedule the last period
G6: =MIN(40000,$C$32-SUM($F$6:F6)) copy across

and a few Iferrors in row 38
upload_2018-4-21_11-40-51.png

Note that the final grade (O38) matches the grade of the last block (D31) as it should

see attached file
 

Attachments

Last edited:
I was going to request permission to withdraw my withdrawal on the grounds that I think I now see the way through, with two separate accumulations for orders and supply. Looks like I have missed the boat :(. The key element is the use of lower triangular matrices to form cumulative distributions rather than relying on the more efficient forms of accumulation that require helper cells.

Looking at John's solution I see elements of this, though I was planning to use LOOKUP to perform matches within memory.
 
Hui said:
For anybody following this along
Using John's modified formula achieved this:
View attachment 51635
Note that all values are correct except the last grade
It is wrong as it has divided by the 40,000tonnes, but there is only 34,292t available.

So a simple redesign of the scheduling row, Row 6, so as not to over schedule the last period
G6: =MIN(40000,$C$32-SUM($F$6:F6)) copy across

and a few Iferrors in row 38
View attachment 51636

Note that the final grade (O38) matches the grade of the last block (D31) as it should

see attached file
Click to expand...
Hi Hui,
Perhaps an idea for formula forensics on the :awesome:-blog?
 
Peter Bartholomew said:
I was going to request permission to withdraw my withdrawal on the grounds that I think I now see the way through, with two separate accumulations for orders and supply. Looks like I have missed the boat :(. The key element is the use of lower triangular matrices to form cumulative distributions rather than relying on the more efficient forms of accumulation that require helper cells.

Looking at John's solution I see elements of this, though I was planning to use LOOKUP to perform matches within memory.
Click to expand...
I'd be interested to see implementation of LOOKUP. I had thought of and tried a construct like below (not working) to return the row position at which SUM equals or exceeds:
=LOOKUP(SUM($F$6:F6),SUM(C10:INDEX(C10:C31,ROW(C10:C31)-9)),ROW(C10:C31)-9)
The issue being I could not build an array of SUMs!
 
You must log in or register to reply here.
Back
Top