# Average Range

#### brm101

##### New Member
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.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I don't have HTMLMaker on this machine so bear with me.
The worksheet that I set up contains the following:

Code:
``````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

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

Replies
11
Views
371
Replies
2
Views
145
Replies
27
Views
686
Replies
1
Views
195
Replies
1
Views
241

1,196,155
Messages
6,013,761
Members
441,781
Latest member
Gian Carlos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back