Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages with formulas
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(P3:P7,0)=5,0,(AVERAGE(IF(P3:P70,P3:P7)) )) week1 =IF(ISERROR(O10/B10),0,O10/B10) Month =IF(COUNT(P3:P7,0)=5,0,(AVERAGE(IF(P3:P70,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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages with formulas
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 "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 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:P70,P3:P7)) )) week1 =IF(ISERROR(O10/B10),0,O10/B10) Month =IF(COUNT(P3:P7,0)=5,0,(AVERAGE(IF(P3:P70,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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages with formulas
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 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:P70,P3:P7)) )) week1 =IF(ISERROR(O10/B10),0,O10/B10) Month =IF(COUNT(P3:P7,0)=5,0,(AVERAGE(IF(P3:P70,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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages with formulas
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. "Mike H" wrote: 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 "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 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:P70,P3:P7)) )) week1 =IF(ISERROR(O10/B10),0,O10/B10) Month =IF(COUNT(P3:P7,0)=5,0,(AVERAGE(IF(P3:P70,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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages with formulas
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 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:P70,P3:P7)) )) week1 =IF(ISERROR(O10/B10),0,O10/B10) Month =IF(COUNT(P3:P7,0)=5,0,(AVERAGE(IF(P3:P70,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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
averages with formulas
On Oct 30, 3:33 pm, Dena 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:P70,P3:P7)) )) week1 =IF(ISERROR(O10/B10),0,O10/B10) Month =IF(COUNT(P3:P7,0)=5,0,(AVERAGE(IF(P3:P70,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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averages/if formulas-need help ASAP!! | Excel Discussion (Misc queries) | |||
Formulas: Averages | Excel Discussion (Misc queries) | |||
Need help with monthly averages, and copying formulas | Excel Worksheet Functions | |||
first ten and last ten averages | Excel Worksheet Functions | |||
Averages | Excel Programming |