Skipping Zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to see how fast my employees scan. I set up a sperad sheet for this. I have it track every week and let me know if they are up or down for the month. Colum 1 has the curent week, Colum 2 has last weeks and colum three is set up to subtract 1 from 2 to get a variance. The bottom of colum one is an average for the week. Here is my trouble. How can I get the average of colum 1 and have it not count any zeros because all my employees don't work every week? I could leave it blank but then I get an error in colum 3 for the variance. The bottom of colum 3 is set up to do a sum of colum 3 so I can see how much we have improved overall for the week. With the errors in colum 3 I get an error on the bottom. So I need to have zero values in colum 1. The computer counts these in the average. ie if I have 20 cashiers and 16 work the computer still divides by 20 instead of ignoring the zeros and dividing by 16. Help me if you can. Thanks
 
Mega, try something like this will not average 0's
=AVERAGE(IF(B5:M5<>0,B5:M5))
array-formula: must be entered by (ctrl +shift+enter)


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
Mega said:
I am trying to see how fast my employees scan. I set up a sperad sheet
Click to expand...
for this. I have it track every week and let me know if they are up or down
for the month. Colum 1 has the curent week, Colum 2 has last weeks and
colum three is set up to subtract 1 from 2 to get a variance. The bottom of
colum one is an average for the week. Here is my trouble. How can I get
the average of colum 1 and have it not count any zeros because all my
employees don't work every week? I could leave it blank but then I get an
error in colum 3 for the variance. The bottom of colum 3 is set up to do a
sum of colum 3 so I can see how much we have improved overall for the week.
With the errors in colum 3 I get an error on the bottom. So I need to have
zero values in colum 1. The computer counts these in the average. ie if I
have 20 cashiers and 16 work the computer still divides by 20 instead of
ignoring the zeros and dividing by 16. Help me if you can. Thanks
 
Small correction of an type error

SUM(A2:A30)/COUNTIF(A2:A30,">0")


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Jerry W. Lewis said:
=SUM(A2:A30)/COUNTIF(A2,A30,">0")

Jerry
Click to expand...
for this. I have it track every week and let me know if they are up or down
for the month. Colum 1 has the curent week, Colum 2 has last weeks and
colum three is set up to subtract 1 from 2 to get a variance. The bottom of
colum one is an average for the week. Here is my trouble. How can I get
the average of colum 1 and have it not count any zeros because all my
employees don't work every week? I could leave it blank but then I get an
error in colum 3 for the variance. The bottom of colum 3 is set up to do a
sum of colum 3 so I can see how much we have improved overall for the week.
With the errors in colum 3 I get an error on the bottom. So I need to have
zero values in colum 1. The computer counts these in the average. ie if I
have 20 cashiers and 16 work the computer still divides by 20 instead of
ignoring the zeros and dividing by 16. Help me if you can. Thanks
 
Give this a try:

=AVERAGE(IF(your rnge,your rnge))
Confirm with cntrl, shift, enter; not just enter

The computer counts these in the average. ie if I have 20 cashiers
and 16 work the computer still divides by 20 instead of ignoring the
zeros and dividing by 16. Help me if you can. Thanks
 
Back
Top