Average of work orders per business days

[Guest]

How could I determine the average of work orders that are received per
business days in a given month? I have two columns: Column B lists a unique
code for each work order and Column C lists the date in which each work order
was created. I don't receive work order every day, but in some days I receive
any number of work orders.

Thank you for your help.

Orlando

 

[Dave Peterson]

For any date in A1, you can find the number of workdays in that month with a
formula like:

=NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)+1,0))

You can count the number of dates within a month by using something like:

=sumproduct(--(text(c1:c10,"yyyymm")=text(a1,"yyyymm"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Then just divide the count by the number of workdays and you'll have an average
per workday.



Then you could just divide that

 

[Guest]

Thank you for your reply, Dave. But I guess I am a newbie and bit confused.
Please let me give you more info:

- My range of dates is B3:B117 where each cell is formated as Date, e.g.
03-Jun-06
- My range of work order codes is B3:B115
- In any given business day of the month (e.g. June) I receive either 1 or n
numbers of work orders
- I want to determine the average of work orders received per business days
only (Mon to Fri) for each month of the year (e.g. June ... each month has is
own worksheet)
- I want to calculate and show the average of the month in Cell B117 (Cell
B116 shows the total of work orders in the month).

Regards,

Orlando

 

[Dave Peterson]

Put a date in that month in say C117.

Then put this in B117:

=(sumproduct(--(text(B3:b115,"yyyymm")=text(c117,"yyyymm")))
/(NETWORKDAYS(DATE(YEAR(c117),MONTH(c117),1),DATE(YEAR(c117),MONTH(c117)+1,0))

You have to give the formula an idea of what month you want to use.

 

[Guest]

I got a #NAME? error... whether Enter or Shift + Ctrl + Enter. By the way,
why doesn't your formulae have dd? How the days are counted?

Cheers,

 

[Dave Peterson]

=networkdays() is a function from the analysis toolpak.

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

Tools|Addins

Depending on how excel was installed, you may need the distribution CD.

After you install that addin, reenter the formula (F2 followed by enter should
be enough).

And take a look at =networkdays() to see how it works. You can even provide a
list of holidays to ignore, too.

And no need to ctrl-shift-enter the formula, either.

I got a #NAME? error... whether Enter or Shift + Ctrl + Enter. By the way,
why doesn't your formulae have dd? How the days are counted?

Cheers,

 

[Guest]

Thank you again for your help. I ran into two situations:

1) After having installed Analysis ToolPak, the formulae returned 0.

2) I also tested the =networkdays() function along and the formulae returned
the total number of business days of the month, but not the actual business
days shown in my range (e.g. some business days are not shown because I did
not received any work order during those days).

Cheers,

Orlando

 

[Dave Peterson]

Did you put a date from the month you were interested in in C117?

If yes, did you have any workorders received in that month?

Thank you again for your help. I ran into two situations:

1) After having installed Analysis ToolPak, the formulae returned 0.

2) I also tested the =networkdays() function along and the formulae returned
the total number of business days of the month, but not the actual business
days shown in my range (e.g. some business days are not shown because I did
not received any work order during those days).

Cheers,

Orlando

 

[Guest]

Yes, the date in C117 is 01-Jun-06 (cell is formatted as Date). And yes,
there are 112 work order codes listed in te range B3:B115.

Thank you for your patience.

Orlando

 

[Dave Peterson]

Where are the dates located for those work order codes. That formula assumes
that those dates were in B3:b115

Yes, the date in C117 is 01-Jun-06 (cell is formatted as Date). And yes,
there are 112 work order codes listed in te range B3:B115.

Thank you for your patience.

Orlando

 

[Guest]

The dates are in the C3:C115 range. I changed the B3:B115 to C3:C115, and I
got 5.7727. But I am not sure about this figure. For example, in June I
received 115 work orders. If I divided 115/22=5.22; where 22 is the total
business days in June. However, in June I only received work orders over 18
business days out of those 22. As a result, 115/19=6.05. Anyway, I have these
two columns: B3:B115 lists a unique code for each work order and C3:C115
lists the date in which each work order was received. I don't receive work
order every day, but in some days I receive n numbers of work orders.

Regards,

 

[Dave Peterson]

Your original question was:

How could I determine the average of work orders that are received per
business days in a given month?

If you want the per business day average (which seems reasonable to me), use the
formula you have.

If you want to only include the business days that you actually got at least one
workorder, then change the denomiator to:

(SUMPRODUCT((TEXT(c3:c115,"yyyymm")="200606")/COUNTIF(c3:c115,c3:c115&"")))

or

(SUMPRODUCT((TEXT(c3:c115,"yyyymm")=text(somecellwiththedateinit,"yyyymm")
/COUNTIF(c3:c115,c3:c115&"")))


It just depends on what you really want.

If you got 115 workorders all on one day and none the rest of the month, what
would you want for the average?

115/1
or
115/22

The dates are in the C3:C115 range. I changed the B3:B115 to C3:C115, and I
got 5.7727. But I am not sure about this figure. For example, in June I
received 115 work orders. If I divided 115/22=5.22; where 22 is the total
business days in June. However, in June I only received work orders over 18
business days out of those 22. As a result, 115/19=6.05. Anyway, I have these
two columns: B3:B115 lists a unique code for each work order and C3:C115
lists the date in which each work order was received. I don't receive work
order every day, but in some days I receive n numbers of work orders.

Regards,

 

[Guest]

Thank you so much, Dave.

Your original question was:

How could I determine the average of work orders that are received per
business days in a given month?

If you want the per business day average (which seems reasonable to me), use the
formula you have.

If you want to only include the business days that you actually got at least one
workorder, then change the denomiator to:

(SUMPRODUCT((TEXT(c3:c115,"yyyymm")="200606")/COUNTIF(c3:c115,c3:c115&"")))

or

(SUMPRODUCT((TEXT(c3:c115,"yyyymm")=text(somecellwiththedateinit,"yyyymm")
/COUNTIF(c3:c115,c3:c115&"")))


It just depends on what you really want.

If you got 115 workorders all on one day and none the rest of the month, what
would you want for the average?

115/1
or
115/22

 

[Dave Peterson]

Hope it worked.

Thank you so much, Dave.