On Oct 30, 3:33 pm, Dena <D...@discussions.microsoft.com> wrote:
> We are tracking our production and our employees. We track on a daily basis
> to give us a total for the week. We need to know if the facilities are
> getting the averages they are benchmarked with each month and for the year.
> My spreadsheet has how many days are worked and how many hours are work for
> the week and then divided by a forty hour work week and one week there can be
> 8 employees, 9.2 employees, and so on but my boss does not want to use the
> same formula that I use for the weeks he wants an average for the month. I
> tries to copy and paste what I have so you have a better understanding but it
> did not work very well.
>
> "Joel" wrote:
> > What does an average mean. Number of workdays, number of days in the week
> > (7), Number of days in a month, does avvarage include holidays?
>
> > COUNT(P3:P7,0) produces a result of 6, not 5.
>
> > "Dena" wrote:
>
> > > I have a spreadsheet that is formulated to give me average employees per day
> > > for the week and I use =IF(ISERROR(O10/B10),0,O10/B10) so if there is no data
> > > I get a zero instead of div/# my problem comes with getting averages for he
> > > month without counting those zero's and then using the averages for the month
> > > to give me an average for the year. I have tried numerous formulas so I do
> > > not get div/# for the month but then my average is off because of the zero's
You can calculate the Average for non-zero days by dividing SUMIF >0
by COUNTIF > 0 for all days in the year. E.g.,
HR_AVG =SUMIF(X11:X22,">0")/COUNTIF(X11:X22,">0")
Just replace the ranges with your range of values.
SteveM
> > > so then I can't get the average for the year because of the div/#. Here is an
> > > example of how it is set up
>
> > > week 1 =IF(ISERROR(O10/B10),0,O10/B10)
> > > week 2 =IF(ISERROR(O10/B10),0,O10/B10)
> > > week 3 =IF(ISERROR(O10/B10),0,O10/B10)
> > > week 4 =IF(ISERROR(O10/B10),0,O10/B10)
> > > Month =IF(COUNT(P3:P7,0)=5,0,(AVERAGE(IF(P3:P7>0,P3:P7))))
> > > week1 =IF(ISERROR(O10/B10),0,O10/B10)
> > > Month =IF(COUNT(P3:P7,0)=5,0,(AVERAGE(IF(P3:P7>0,P3:P7))))
> > > YTD I can not figure out
>
> > > For the monthly formula I can get a correct average as long as I have data
> > > in my cells but if not I get div/# then my ytd will not work.
>
> > > Someone please help I have been working on this for a week now!
|
Similar Threads
