I have been designated to create a spreadsheet which tracks a lot of
information, and does a trend analysis for only three weeks worth of
information. So far, I have set up the sheet horizontally.

So... Row 1 = Week 1, Week 2, Week 3, Week 4, etc.
Row 2 = values for each week. (ie 1, 2, 3, 4, etc) which are being based on
a different sheet inside the same file, so they are linked to each other.
Therefore, if the other sheet contains no data for the new week, a 0 is made
(note: there will never be a zero field for the week value.)

With that said. I now have to find a way to only do a three week analysis,
where it averages the three weeks before the zeros.

Say we are right now in week 5. This means that weeks 1-4 have values, and
weeks 5 - 52 have zeros. I would need to average weeks 2, 3 & 4 together.
And thus, this would continually update itself after new information has been
entered into the other data table. When we are in week 6; 3, 4, & 5 would
average together.

Any help would be greatly appreciated.