 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2006Average of work orders per business days
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 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 > 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 [quoted text clipped - 5 lines] > > Orlando  SignatureDave Peterson 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 > For any date in A1, you can find the number of workdays in that month with a > formula like: [quoted text clipped - 30 lines] > > > > Orlando 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. > Thank you for your reply, Dave. But I guess I am a newbie and bit confused. > Please let me give you more info: [quoted text clipped - 52 lines] > > > > Dave Peterson SignatureDave Peterson 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, > Put a date in that month in say C117. > [quoted text clipped - 61 lines] > > > > > > 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? [quoted text clipped - 70 lines] > > > > Dave Peterson SignatureDave Peterson 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 > =networkdays() is a function from the analysis toolpak. > [quoted text clipped - 87 lines] > > > > > > 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: > [quoted text clipped - 104 lines] > > > > Dave Peterson SignatureDave Peterson 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 > Did you put a date from the month you were interested in in C117? > [quoted text clipped - 108 lines] > > > > > > 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. [quoted text clipped - 119 lines] > > > > Dave Peterson SignatureDave Peterson 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, > Where are the dates located for those work order codes. That formula assumes > that those dates were in B3:b115 [quoted text clipped - 122 lines] > > > > > > 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 [quoted text clipped - 137 lines] > > > > Dave Peterson SignatureDave Peterson Thank you so much, Dave. > Your original question was: > [quoted text clipped - 164 lines] > > > > > > Dave Peterson Hope it worked. > Thank you so much, Dave. > [quoted text clipped - 170 lines] > > > > Dave Peterson SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.