Hi
Averaging times. I have a column of racing times. The column to its right contains the average of the times starting from that line and every line above it. I was proud of myself for figuring out the formula to do it - until I realized that I was averaging it as if were a decimal number (158.4) instead of time (1:58.4) - One minute : fifty-eight seconds . and four fifths of a second. The last number (e.g. ".4")is from zero through 4 (or blank), and represents fifths of a second (Strange, but it's the way they do it.). The format (there is no choice) that it will have been previously input in and then automatically (by a button) copied to the "Times" column (DO) will be xxx.x . If it helps, I can have the DO and DP columns and their numbers formatted in any manner, if needed.
Is there a way to display an average on each line? Do the times have to be first put into the format of x:xx.x ? (-and if so, then how could I do that by formula or VBA?)
I would appreciate any help!
"Times" column: DO ... "Averages" column: DP ... Lines: 194 through 358
Here's what I *was* using on each line in DP:
=IF(OR($DO198="", $DO198>"*"),"",AVERAGE($DO$194:$DO198))
Exception: Certain times do not get included in any average. There is an asterisk after those (e.g. 202.1*). I was experimenting and found, to my surprise, that $DO194>"*" in my formula worked to find and exclude those times with asterisks. (Works for other characters, also.)
Thanks -SteveC
This was extensively edited on 10/5 09:50
This message was edited by SteveC on 2002-10-08 03:55
Averaging times. I have a column of racing times. The column to its right contains the average of the times starting from that line and every line above it. I was proud of myself for figuring out the formula to do it - until I realized that I was averaging it as if were a decimal number (158.4) instead of time (1:58.4) - One minute : fifty-eight seconds . and four fifths of a second. The last number (e.g. ".4")is from zero through 4 (or blank), and represents fifths of a second (Strange, but it's the way they do it.). The format (there is no choice) that it will have been previously input in and then automatically (by a button) copied to the "Times" column (DO) will be xxx.x . If it helps, I can have the DO and DP columns and their numbers formatted in any manner, if needed.
Is there a way to display an average on each line? Do the times have to be first put into the format of x:xx.x ? (-and if so, then how could I do that by formula or VBA?)
I would appreciate any help!
"Times" column: DO ... "Averages" column: DP ... Lines: 194 through 358
Here's what I *was* using on each line in DP:
=IF(OR($DO198="", $DO198>"*"),"",AVERAGE($DO$194:$DO198))
Exception: Certain times do not get included in any average. There is an asterisk after those (e.g. 202.1*). I was experimenting and found, to my surprise, that $DO194>"*" in my formula worked to find and exclude those times with asterisks. (Works for other characters, also.)
Thanks -SteveC
This was extensively edited on 10/5 09:50
This message was edited by SteveC on 2002-10-08 03:55
