Calculating a moving weighted average during Resource Scheduling

[Peter Bartholomew]

single formulae which can be understood by barely 1% of Excellers

As many as that?
The ICAEW regard about 1‰ of users to be developers and I have a feeling that Lori's formulae would leave many of those floundering!

In most other respects udfs are the way to go

Or full-blown compiled code? That might, though, underrate the value and flexibility of Excel as a development/prototyping environment.

Much of the problem comes in the context of the final packaging of a solution. The choice may be helper cells or more deeply nested formulae (with the later often requiring array algebra rather than working at the component, cell level). My use of named formulae is a compromise; it reality it is simply building a nested formula but by naming the inner elements it can be understood as a sequence of steps (except for the fact that, for the majority of users, a named formula is absolutely opaque, even when the name itself is informative).

As @Hui points out, helper cells are not always even an option. A large 2D range of helper cells that varies from row to row (in reality, representing a 3D array) is never going to look good on a worksheet! That is not to say that I wouldn't lay out an instance or two of the array while developing and checking the formula. It then has to be packaged until an aggregation operator provides user-intelligible output.

 

[Peter Bartholomew]

I will be doing a FF post on it in due course.

Hi @Hui Did that happen? It was something of a classic challenge.

Meanwhile so much has changed in the world of Excel:

First, dynamic arrays allowed be to get rid of CSE and I no longer needed to hide formulas in Name Manager to avoid the dreaded implicit intersection. With it came a number of array functions that bypassed the semi-manual processes of filter and sort beloved of true Excel jockeys.​

​

Second, what was for me, something of a surprise was the LET function, that allowed me to unpick the deeply nested formulas and, at the same time, enabled local variables to be used for intermediate steps. 10-20 lines for a formula became the norm and my worksheets were spared the reems of pointless sheet-junk (like Tufte's chart junk but not nearly so eye catching) that used to fill the sheet in the form of helper ranges.​

​

Third, Lambda functions took the huge step of allowing parameters to be passed to formulae, making them reusable. Recursion was a thing and could achieve results that were otherwise only possible with VBA, but I found them intensely difficult to use effectively.​

​

Finally, the Lambda helper functions provided the results of recursion without the pain.​

I remembered @Hui 's post as providing a pretty major challenge at the time, so I decided to return to it with new tools to see how they performed. I had written a [very] small library of λ-functions that I thought might help including Accumulateλ [generates running totals] and Allocateλ [written with FIFO calculations in mind]. All I had to do was copy the functions into the existing workbook. At least, that was the idea ...

= Allocateλ(
      OreDemand∕period,
      OreSupply∕parcel,
      Fe.percent)
   / OreDemand∕period

The formula allocates the ore supply to the demand per period. The additional parameter weights the supply parcels by the percentage iron and sums to give the total iron content per period. Dividing by the total weight of ore for the period to give the percentage for the period.

My conclusion is that a very different development practice is available within 365 from that needed for legacy Excel. Not everything is gain; a lot more thought goes in to producing a solution. The activity is very much one of programming; something that most Excel users would prefer to avoid?

 

[Hui]

Hi @Hui Did that happen? It was something of a classic challenge.
.
.
.
My conclusion is that a very different development practice is available within 365 from that needed for legacy Excel. Not everything is gain; a lot more thought goes in to producing a solution. The activity is very much one of programming; something that most Excel users would prefer to avoid?

Hi Peter

That is an awesome solution

No, The FF Post never happened, I have been snowed under with work commitments and you probably notice that I even rarely post here any more.

The new Dyanmic stuff is really really interesting.
There are many advances that simple things, like Sort, Unique and Filter add to existing models, purely by simplification of data flows that they are great, but add Lamda into the mix and lookup

I suspect that Lambda is going to add a level of complexity that may take a while for normal users to adjust to

and I can already see how Lambda should have been extended into the 3rd dimension by adding multiple functions within a Lambda, ie: Full on programming within a Lambda...Just my guess

I'm going to need to get my head over the solutions your provided but they are very clever. Thankyou

Hui...

 

[Peter Bartholomew]

@Hui
Just to complicate things, I have added a further solution to the problem of estimating Fe content per output batch.
Whereas the first solution depends upon MAKEARRAY to build the 2D array showing what tonnage of each output was obtained from any input, the new solution uses nested MAP helper functions. The inner part of the formula focusses on a single output and returns the tonnage derived from each input parcel (mainly zeros). It then applies the Fe percentage and sums to get the monthly Fe content as a scalar. That allows the second MAP to scan over the output, returning Fe content for each month.

The advantage of the new approach is that the core formula

= MAX( MIN(v₁, u₁) - MAX(v₀, u₀), 0) * w)

determining the overlap between two intervals [u₀, u₁] and [v₀,, v₁] is more readable.

Again, I did not write the Lambda function specifically for your challenge. It was written for another purpose and then simply applied to this problem by cutting and pasting the formula between workbooks and selected the relevant arguments.

Peter