# Calculating a moving weighted average during Resource Scheduling

#### 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:

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:

Sample file attached

Look forward to any and all assistance

#### Attachments

• Scheduling2.xls
47 KB · Views: 20
Last edited:

Hi !

I suppose you do not want a VBA calculation (quite easy) …​

No thanx

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

• Scheduling2PB.xls
52.5 KB · Views: 5
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:

Without using the block of helper cells

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 !​
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

• Scheduling2X.xls
64.5 KB · Views: 6
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.

Maybe this is moving in the right direction.
So far just one period with 5 parcels.

#### Attachments

• Scheduling Sample.xlsx
13.8 KB · Views: 8
Time to give in on that one! Min you, I rather like the 2D array and found it helpful.

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!
I quietly admire this formula, blink and go numb after a while.

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!

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

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:
Maybe this is moving in the right direction.
So far just one period with 5 parcels.

Peter

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:

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

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

see attached file

#### Attachments

• Scheduling2.xlsx
17.5 KB · Views: 21
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.

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
Hi Hui,
Perhaps an idea for formula forensics on the -blog?

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