Average Calculation

[Munchkin]

I need to calculate the average client visit days for our sales staff & I'm
stumped. Last year I entered each week's total visit days separately and my
spreadsheet was large. This year I thought I'd be clever and enter a monthly
total instead.

I created an abbreviated example of my spreadsheet below. I feel silly, but
I can't figure out how to get the average visit days per week based on the
total visit days per month. Can you help with the calculation formula?
Thanks!

Visit days per week: ?
Total visit days : 25
Name: John Doe
Jan 7
Feb 10
Mar 8

 

[caroline]

Add all the months to get the year total then divide by (365/7)

 

[Bernard Liengme]

To clarify Caroline's reply:
=SUM(the 12 month values)/365 gives the daily average if you are open 365
days of the year
So =SUM(the 12 month values)*7/365 will give the weekly average if you are
open 7 days or the week
For the purpose of this average (it accuracy surely need no be very great),
I would not be too concern about holidays (just weekends)
Just adjust 365 and 7 to suit the actual times of operation
best wishes

 

[Shane Devenshire]

Hi,

The reason you are having a problem is that there really is no accurate way
to get the results you want. First you need to define what counts as visit
days, then you need remember that each month has a different number of days.

 

[JP Ronse]

Hi,

Not sure I understand your question correctly but maybe this can give an
idea.

Workingdays Visits Weeks Visits/week
Jan 21 7 4,2 1,6666667
Feb 20 13 4 3,25
Mar 22 15 4,4 3,4090909
Apr 22 18 4,4 4,0909091
May 20 13 4 3,25
Jun 22 13 4,4 2,9545455
Jul 23 12 4,6 2,6086957
Aug 21 14 4,2 3,3333333
Sep 22 16 4,4 3,6363636
Oct 22 16 4,4 3,6363636
Nov 21 16 4,2 3,8095238
Dec 22 16 4,4 3,6363636


In the first column the first day of each month, formatted custom 'mmm'.
The second column counts the workingdays based on a wokweek from Monday to
Friday
=NETWORKDAYS(A2,DATE(YEAR(A2),MONTH(A2)+1,0),$Z$1:$Z$3)

In range Z1:Z3 I've put the official holidays like New year, Xmas, ...

In column 3 you have the number of weeks for each month (column 2/5)

In column 4, the average number of visits per week: visits/weeks.

You mat have to install the Analysis Toolpak: Tools/Add-ins.

If you have another workweek, e.g. Tuesday to Saturday or even a 6-day week,
see Chip Pearson's page for a better function.

http://www.cpearson.com/excel/betternetworkdays.aspx

Uou can of course combine several columns:

="visits"/(NETWORKDAYS(A2,DATE(YEAR(A2)MONTH(A2)+1,0),$Z$1:$Z$3)/5)



Hope this helps.

Wkr,

JP