question on formulas
 

Hello,
I have a spreasheet that calucation time in seconds in a static column (L).
like this,
45.25
26.25
The number of rows with data will change daily. and these are also not
Numeric fields, but text fields. So to change over to numeric, i use the copy
1 on a cell and paste special valuesmultiple to make it to a numeric field.

On row 40, which is the total for this type of spreadsheet, i want to
calculate an average for the seconds, as described in column L above. But,
since i do the past special multipy by 1, the empty cells have "0" in them,
thereby throwing off the average on row 40.
any thoughts on how to calculate the average on row 40.. (can't use the IF
statement becuase sometime the row will be a "0"seconds rather than 26.25
seconds.
Thanks in advance

question on formulas
 

Forget the transforming, use this formula directly on the text cells

=SUMPRODUCT(--(A1:A39))/SUMPRODUCT(--(A1:A39<""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"D" wrote in message
...
Hello,
I have a spreasheet that calucation time in seconds in a static column
(L).
like this,
45.25
26.25
The number of rows with data will change daily. and these are also not
Numeric fields, but text fields. So to change over to numeric, i use the
copy
1 on a cell and paste special valuesmultiple to make it to a numeric
field.

On row 40, which is the total for this type of spreadsheet, i want to
calculate an average for the seconds, as described in column L above. But,
since i do the past special multipy by 1, the empty cells have "0" in
them,
thereby throwing off the average on row 40.
any thoughts on how to calculate the average on row 40.. (can't use the IF
statement becuase sometime the row will be a "0"seconds rather than 26.25
seconds.
Thanks in advance