Skipping Zeros

[Mega]

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

 

[Paul B]

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 **

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

 

[Jerry W. Lewis]

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

Jerry

 

[A.W.J. Ales]

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 *

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

Jerry

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

 

[2rrs]

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