• 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


  • 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

Peter Bartholomew

Well-Known Member
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.