(e-mail address removed) wrote...
I have a project that collects data from multiple worksheets. On each
sheet one cell keeps a count of times a person is in the office. What
I want to do is have one formula that will average each of these
counts only if the count is greater than zero. I can get the average
of all of them together but the total comes out inaccurate because of
the number of sheets that haven't had information put into them yet.
I've hit a pretty big road block and my deadline is looming...Any
advice?
Are these cells always at the same address in each of these
worksheets, e.g., always in cell C5? If so, and if you've tried using
something like
=AVERAGE('first worksheet:last worksheet'!C5)
but it's giving the wrong result, does that mean you have zeros in the
C5 cells when the person in question hasn't been in the office at all?
If so, have you considered clearing the zeros so your formula would
ignore the then blank cells?
If you can't clear the zeros, then consider using another cell in each
worksheet, say, CC5 containing the formula
=IF(COUNTIF(C5,">0"),C5)
which would evaluate to the value in C5 if it's a positive number and
to the boolean value FALSE otherwise. Excel's AVERAGE function ignores
FALSE values, so the formula
=AVERAGE('first worksheet:last worksheet'!CC5)
would then return the correct average.
If you can't modify the source worksheets at all, then if all the
values from the other worksheets would be zero or positive numeric
values, try the following formula.
=SUM('first worksheet:last worksheet'!CC5)
/INDEX(FREQUENCY('first worksheet:last worksheet'!CC5,{0}),2)
If there could be anything in the C5 cells in the other workbooks and
you only want to average the positive numbers, you're going to have to
pull each of those worksheets' C5 values into your summary worksheet
INDIVIDUALLY, then average those values. For example, in your summary
worksheet,
B7: =IF(COUNTIF('first worksheet'!C5,">0"),'first worksheet'!C5)
B8: =IF(COUNTIF('second worksheet'!C5,">0"),'second worksheet'!C5)
B9: =IF(COUNTIF('third worksheet'!C5,">0"),'third worksheet'!C5)
:
B##: =IF(COUNTIF('last worksheet'!C5,">0"),'last worksheet'!C5)
Then use =AVERAGE(B7:B##) to average only the positive numbers in
these C5 cells.
Finally, you could enter the worksheet names in a list in your summary
worksheet, name that list wslst, and use a formula like
=SUMIF(INDIRECT("'"&wslst&"'!C5"),">0")/COUNTIF(INDIRECT("'"&wslst&"'!
C5"),">0")
