Y-T-D averages

[LaD]

Here's what I'm struggling with. See the attached test sheet. I need
formulas for lines 34, 50, and 66. Line 34, for example, should average
D6 thru D17 and D20 thru D32. The formula also has to ignore zero values or empty cells in case there is no value for a week and because future weeks will be blank. The y-t-d average has to keep adding the latest week of data. This is beyond me at this point. Any help would be really appreciated.
TEST sheet.txt

[FrankBoston]

Maybe this idea will help. Say your values are in B4:B9, then the following will average only the non-zero values.

=SUM(B4:B9)/COUNTIF(B4:B9,"<>0")

[LaD]

How would the formula look if I want to average different groups of cells like B4:B9 and B14:B19 and B23:B28 for example? To get my y-t-d averages I'll be pulling numbers from 1Q, then 1Q & 2Q, then 1Q, 2Q, 3Q, etc.

[FrankBoston]

Uploading your workbook would make things understandable. Place the XLS into a zipped folder and upload the folder.

[LaD]

Here's the zip file.

[FrankBoston]

See the attached workbook for the implementation.

These formulas use the presence of a Week Number in Col A only for rows
holding data, not subtotals.

Average for the prior 13 rows

=SUMPRODUCT(--(A20:A32<>0), D20:D32) / SUMPRODUCT(--(A20:A32<>0), --(D20:D32<>0))
                  A            B                      A1              B1
Term A : 1 if there is a Week number in Col A, 0 otherwise
         Keeps other subtotals out of the sum
Term B : All of the amounts for the 13 weeks, including 0's
Term A1: Same form and purpose as A
Term B1: 1 if the amount is <> 0,  0 otherwise. This counts only the non-zero
         quantities for making the average

The '--' in front of the terms converts the logical value into 0 for False, 1 for 

True

---
Average for all of the rows

=SUMPRODUCT(--(A$6:A32<>0), D$6:D32) / SUMPRODUCT(--(A$6:A32<>0),--(D$6:D32<>0))

This is the same as the first formula, except that the start row is fixed at
row 6 by using A$6 instead of say A6.