Rolling Average Formula

The data layout is not conducive to easy analysis
(it would be better from a functional standpoint to have 3 columns per transaction: date|shift|value ordered by date, shift)

As you have it:

Please Login or Register  to view this content.

would work given sample data but it's far from ideal

that said there will be a simpler way for sure.

edit:
an even more convoluted method (not array but Volatile)

Please Login or Register  to view this content.

The formulae should work as they are so I presume you're referring to the suggested revision to layout ?

When storing data which is to act as source for analysis (ie formulae) it's generally a good idea to think along the lines of database structure.

In this case you have two key fields per value: Date & Shift

It follows you have three shifts per date.

You should therefore store data in the following manner:

Please Login or Register  to view this content.

ie Shift becomes a vertical vector - each "transaction" (row) represents a single data point.

It follows that subsequent last "n" type analysis is fairly straightforward given the values are stored in a consistent manner.

You can work with the data as you have it but it's slightly more cumbersome to work with down the line.

The above may appear more cumbersome in terms of data entry but it isn't really and the benefits are likely to outweigh the costs long-term.
If really nec. you can continue to input as you are and use a load of formulae to transpose the data set for use in later analysis

My bad - the offset adjustment of -10 is incorrect and should be -9

Please Login or Register  to view this content.

I'd probably still opt for the Array ahead of the above mind you (if I couldn't reorganise the data in first instance of course!)

edit: scrap that - the 9 needs to be 10 where the last active row is incomplete (ie add a MOD test result to the 9)

Corrected version

Please Login or Register  to view this content.

"urgh" springs to mind...

A slightly shorter non-array / non-volatile version:

Please Login or Register  to view this content.

edit:
Perhaps worth pointing out though that the Array (post #2) will also account for possibility of numbers not being listed in sequence (blanks interspersed)

Originally Posted by frank26003

using the very first formula you provide allowed me to not insert a value (for example Sunday when no production takes place) and it does not affect the 30 shift total.

Per the edit in my last post:

Originally Posted by D.O

Perhaps worth pointing out though that the Array (post #2) will also account for possibility of numbers not being listed in sequence (blanks interspersed)

In the case of the Array (the very first formula provided) - if the blank is to be counted in the Averages then I would suggest you enter 0 for those shifts (greater transparency IMO).

Originally Posted by frank26003

simply removing one number (and leaving that cell blank) will give you a different answer using the last three formulas you provided Donkey

A few formulae posted were flawed - the below should all return the same results (point re: blanks excepted)

Please Login or Register  to view this content.

I would be inclined to use the last depending on the issue of interspersed blanks - if such values shall exist and are to be ignored then use the Array.