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.

Please Login or Register  to view this content.

[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.

Please Login or Register  to view this content.

[LaD]

Absolutely incredible! I have no idea how it works, but it does work. Being an excel novice, I was trying to use =Average formulas since the results I needed were averages. I obviously have a lot to learn. Thanks for taking your time to work on this. I'm blown away.