Y-T-D averages

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")

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

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.