Averaging weeks before all weeks are complete

My inventory workbook works off of a 4 week cycle.

Each week has its own sheet.

Inventory is done each week on the inventory sheet and the information goes to that corresponding week

See Attachment

What I am particulary intereted in is the Period Totals worksheet column K

I want to find the average before all four weeks are complete.

Not sure how to do this. Keep in mind that the ending inventory for one week becomes the beginning for the next week, but when there is no ending for that next weeks inventory, the calculation thinks it uses that amount of inventory, thus listing as usage before the week is over, which in turn will also making averaging wrong. Check out out row 5 of the week 1 and week 2 which the usages end up on row 6 of period totals sheet.

Any suggestions.

Please note my profile list my version as 2010, but the version that needs to be compatible with is 97-03

Thanks in advance

Jason

Sorry about that,

Week 1 H5 is beginning inventory that number comes from E5 of Inventory sheet

Week 1 L5 is ending inventory that number come from F5 of Inventory Sheet

Week 1 M5 is the usage of =H5+I5+J5+K5-L5 (week 1 worksheet)

Week 1 M5 is sent to Period Totals E6

Week 1 L5 "ending" becomes Week 2 "beg" H5

When Week 1 is complete I know that on Period Totals sheet that the usage of 170 (E6) is factually true. I know that Week 2 on the Period Toals sheet is not factually ture yet, because that week has not been completed.

So lets assume that Week 1 is complete and accurate, I want in K6 of my period totals sheet to have the average of just week 1 and not weeks 2,3 and 4. As the weeks go on I want K6 to be able to recalculate the average for the completed weeks.

Hope this helps and thanks for your patience

Jason