Every day I process microfilm for 3 hours, Monday - Friday. I am tracking
the time that this is done, and averaging the time at the end of the week in
a spreadsheet. (I am using Excell 2003) If I work on the microfilm 3 hours
for five days the average for the week is 3 hours - easy.
However, the work is not steady and some days there is no microfilm to
process - So I may work 3 hours for 5 days one week, or for 4 days the next
week, or none at all! I still want an average of 3 hours no matter the
number of days , because that is how long the average time to process the
microfilm is for each day. And if the time ever changes - like we get new
equipment that is faster and cuts the time down to 2 hours, I would like that
reflected in the calculations also.
I hope that I am overlooking something simple, but everytime I try to wrap
my brain around this it seems to snow ball out of control! Any suggestions?
Thanks in advance,
~Tim
Bob Phillips - 06 Mar 2008 20:53 GMT
=AVERAGE(IF(A1:A5<>"",A1:A5))

Signature
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
> Every day I process microfilm for 3 hours, Monday - Friday. I am tracking
> the time that this is done, and averaging the time at the end of the week
[quoted text clipped - 18 lines]
> Thanks in advance,
> ~Tim
Bob Phillips - 06 Mar 2008 20:53 GMT
forgot to mention that is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.
Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

Signature
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
> Every day I process microfilm for 3 hours, Monday - Friday. I am tracking
> the time that this is done, and averaging the time at the end of the week
[quoted text clipped - 18 lines]
> Thanks in advance,
> ~Tim