30 Shift Rolling Average

[tandchas]

I am looking for a formula that will compute a rolling average of the raw tons produced for the most recent 30 shifts worked. In the attached spreadsheet, I am looking for a formula for cell H71 which will do the following: beginning with the most recent data entered, add column G until we reach "greater than or equal to 30 AND less than 31". (In the attached example that would be Cell G22). Then provide an average for the values in Column H which are associated with the most recent 30-31 shifts. (In the attached the answer would be 1434/30.5 which equals 47.02.

I will be entering new data each day and this number will "roll".

Thank you for any assistance to my ignorance! Much obliged!

(Unfortunately something is prohibiting me from attaching the file - any thoughts?)

[BobBridges]

No thoughts about the "something" prohibiting you from attaching the sample, until you describe in more detail. What exactly prohibits you? What are the symptoms? Do you fall ill every time you try? Does the IRS spring an audit on you, thus distracting you? Or (just guessing here) do you get some kind of error message, and if so, what does it say?

Meanwhile this doesn't sound too hard. I expect you're adding new shifts by typing in a new row at the bottom of a worksheet or inserting a new one at the top, right? If the latter, you want a formula that will average one of the columns in the top 30 rows—data rows, excluding the worksheet headers if any—but a formula that at the same time won't "follow" the data down as you insert new rows.

If you want the bottom 30 rows, I'm sure it's almost as easy but I'm thinking about how to spot the last row in a formula. Of course you can write a worksheet function of your own to do this, but it oughta be easy enough using just standard worksheet formulae.

[tandchas]

Apparently IE did not like the attachment but Chrome does. In any case, it is now attached.

I have it setup with the newest data at the bottom but it could just as easily be setup the other way.

You'll see, however, that sometimes a shift is not run - I do not want that data if the shift is zero. I need the most recent 30-31 which produced tonnage.

[BobBridges]

I see now. Well, if I were doing this I'd write a worksheet function after all. But there are some clever folks here at doing things with worksheet functions. Anyone else here want to take a whack at this?

If no one else chimes in, tandchas, I'll recommend writing your own personalized average function in VBA. Are you up for that? I more the teach-a-man-to-fish type, but there are some folks here who won't mind just writing one for you. Either way is fine; I understand that no everyone wants to take the time to learn a new skill.

(I'm not being sarcastic. I pay my sons to fix my car so I can concentrate on computers. I could to maintain my own car, but this is more fun.)

[PressF1]

I think there are two ways of looking at this. First is how you structure your data - often people constrain the problem by how they setup their worksheet or by how others ask for the information to be presented. If you added information in reverse chronological order (i.e. add new data at the top of the table) there is an easier solution.

Anyway, using the structure you have provided try adding a reverse counter in column F (in F68 =G68+F69). Then your formula should look like this:
=SUMPRODUCT(H15:H68,N((F15:F68)<31))/SUMPRODUCT(G15:G68,N((F15:F68)<31))

Remove the hardcode 31 and replace it with a reference to a cell where you can change it if requirements change (e.g. average of the last 20 shifts) and you will have a dynamic solution.

The disadvantage of this solution is that the SUMPRODUCT is an array formula and the more data you have in your workbook where you use this formula may slow down calculation times.

[Pecoflyer]

As it seems that the N function might be unreliable, I would replace it with the double unarry operator. As for SUMPRODUCT being an array formula, that is correct but is much faster than usual array formulas ( CSE formulas)

[tandchas]

Thank you for the replies. As I am not opposed to structuring the data differently, could you elaborate how things would be easier if the newer data was at the top; reverse chronological order? Thanks - much appreciated.

[PressF1]

If you structure the data in reverse order and add move your total function to the top of the table directly under the headers then you only need to sum the cells when you hit 30. You can use an INDEX, MATCH combination to do this. It saves using the SUMPRODUCT formula. I recommend using the MATCH at the top of the flag column as this will be a calculated constant that you don't need recalculating in each cell. In a large workbook this will save you calculation speed.

Peco was right in his comment, in isolation. In large workbooks with abundant data the length of the array in repeated array formulas will affect your calculation speed and usability of the file. How much depends on repetition and volume of data. INDEX uses an array so take care if your workbook is a large one.