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