averages with formulas

[Dena]

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
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(P37,0)=5,0,(AVERAGE(IF(P37>0,P37))))
week1 =IF(ISERROR(O10/B10),0,O10/B10)
Month =IF(COUNT(P37,0)=5,0,(AVERAGE(IF(P37>0,P37))))
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!

 

[Mike H]

Hi,

To get an average for numbers in a range different than zero try

=AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))

enter with Ctrl+Shift+Enter

Mike

 

[Joel]

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(P37,0) produces a result of 6, not 5.

 

[Dena]

I tried it and it worked as long as I did not have all zeros in the month. I
guess the problem is that because of the zeros in the months that we have not
gotten too yet it is giving me div/# and then my YTD average does not
understand it.

 

[Dena]

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.

 

[SteveM]

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.

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