Average Range

[brm101]

I would like to know if it is possible to automate this formula.
=((AVERAGE(C3:C6,C9:C13,C16:C20,C23:C25,C27,C30:C32)*11)+(AVERAGE(C7,C14,C21,C28)*2))+C34

What I am calculating is the average of Monday-Friday then multiplying the average by the total number of Mon-Fri left in the month. I am also averaging Saturdays then multiplying the average by the total number of Sat. left in the month. Then adding those two results to the sum of the values month to date. The mulitiplier changes as new data is populated.

In column "A" I have the date and in column "B" I have the day of week that corresponds with the date.

Any help would be greatly appreciated.

 

[SydneyGeek]

I don't have HTMLMaker on this machine so bear with me.
The worksheet that I set up contains the following:

A2:A27   Days excluding Sundays, for November
B2:B27   Day of week
C2:C27   Value to be forecast

E and F are labels as follows:
E2   Today
E3   M-F To Date
E6   Sat to Date
E9   M-F Remaining
E10  Sat Remaining
E12  Forecast
F3   Count
F4   Sum
F5   Avg
F6   Count
F7   Sum
F8   Avg
F9   Count
F10  Count

Formulas:
B2  is =WEEKDAY(A2), filled down to B27
G2  is =TODAY()
G3  is =SUMPRODUCT((WEEKDAY(A2:A27)<7)*(A2:A27<=G2))
G4  is =SUMPRODUCT((WEEKDAY(A2:A27)<7)*(A2:A27<=G2)*C2:C27)
G5  is =G4/G3
G6  is =SUMPRODUCT((WEEKDAY(A2:A27)=7)*(A2:A27<=G2))
G7  is =SUMPRODUCT((WEEKDAY(A2:A27)=7)*(A2:A27<=G2)*C2:C27)
G8  is =G7/G6
G9  is =SUMPRODUCT((WEEKDAY(A2:A27)<7)*(A2:A27>G2))
G10 is =SUMPRODUCT((WEEKDAY(A2:A27)=7)*(A2:A27>G2))
G12 is =G5*G9+G8*G10+G4+G7

Denis

 

[brm101]

This works great! Thanks for the help. This will save me a lot of time when calculating this formula.