Average Function (include Blank Cells and Zeros)

Hi, can someone help me?

My "final" work*** is linked to another "input" work***. I wish for
my final work*** to mirror exactly whatever is in the input
work***. For example, weekends/non-working days/holidays are "blank"
while weekdays/working days are filled with numbers (including zeros on
working days whenever there's no order). I've already accomplished this
part.

However, another dilemma arises when I have to average the daily
figures by the number of calendar days for all departments. Note that
all departments update their figures on different times. For example,
as of June 8, Operations dept may have already updated their figures as
of latest date (June8) while Marketing has only updated as of June 7.

Also, I don't want to update the daily average calculation (changing
the divisor depending upon the latest no. of calendar days) everytime a
department updates their figures. Is it possible to just have a
standard average formula for all departments?

My "final" work*** as of June 8,
Operations Dept (sum divided by 8 working days)
June1(Mon) = 34
June2(Tue) = 31
June3(Wed) = 0
June4(Thur) = 23
June5(Fri) = 21
June6(Sat) = " "
June7(Sun) = " "
June8(Mon) = 23
June 9 onwards is still blank.

Marketing Dept (sum divided by 7 working days)
June1(Mon) = 34
June2(Tue) = 31
June3(Wed) = 0
June4(Thur) = 23
June5(Fri) = 21
June6(Sat) = " "
June7(Sun) = " "
June8 onwards is still blank.

Is there a solution to my problem? Thanks in advance for the help! =)

.

Loading