1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by Hui, Apr 19, 2018.

  1. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,522
    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:
    [​IMG]

    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

    Attached Files:

    Last edited: Apr 20, 2018
  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    Hi !

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

    Hui Excel Ninja Staff Member

    Messages:
    11,522
    No thanx
  4. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

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

    Hui Excel Ninja Staff Member

    Messages:
    11,522
    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...
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    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 !​
  7. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    @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

    Attached Files:

  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,522
    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
  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  10. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    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 …
  11. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    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.

    Attached Files:

  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,522
    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
  13. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    481
    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!
  14. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,940
    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.
  15. Peter Bartholomew

    Peter Bartholomew Active Member

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

    Attached Files:

  16. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    Time to give in on that one! Min you, I rather like the 2D array and found it helpful.
  17. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    781
    I quietly admire this formula, blink and go numb after a while. o_O
    Khalid NGO and NARAYANK991 like this.
  18. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    You should visit this website if you want more of such stuff :

    https://excelxor.com/

    Narayan
  19. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,522
    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: Apr 21, 2018
  20. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,522
    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.
  21. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,522
    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

    Attached Files:

    Last edited: Apr 21, 2018
  22. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    381
    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.
  23. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    781
    Cool one, thx. Had not found it before.
  24. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    781
    Hi Hui,
    Perhaps an idea for formula forensics on the :awesome:-blog?
    Khalid NGO likes this.
  25. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,909
    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!

Share This Page