Average to date

[metab65]

I hope I explain this right....Need help......PLEASE...

I have four columns: Date, Hour, Pieces, Pieces per hour (I have
formula set up in this column for divide the number of hours by th
number of pieces. Every day info is put into the next row and it goe
on and on.........

I need to have an average to date at the bottom of it of the pieces pe
hour.

It's like 20 rows long (and will continue getting longer as more row
are added.

Here's the problem. Some of the cells in the column of total pices pe
hour don't contain a number yet (because w/those rows, the data has no
been added yet). Those particular cells contain a formula =SUM(K9/J9
that hasn't been performed yet as the figures aren't in sells K9 and J
yet. Those cells are displayed as #VALUE!

What I want is: To get an average to date of only the cells tha
contain numbers and as data in a new row is added, the average will no
change because a new row has been added..

DOes this make sense?????

Thanks to anyone who can help....

By the way, I tried the AVERAGE thing with greater than 0, and i
didnt' work.

Maggi

 

[Frank Kabel]

Hi
one way (if your values are stored in column L)
=SUMPRODUCT((NOT(ISERROR(L1:L1000)))*(ISNUMBER(L1:L1000)),L1:L1000)/SUM
PRODUCT((NOT(ISERROR(L1:L1000)))*(ISNUMBER(L1:L1000)))

if your data starts in row 1