Calculating average timings (with a slight twist)

[Shadowboxer]

I wish to calculate the averages timings (of a vehicle to pass between two points) across a number of columns but to be able to deselect some entries at an unspecified future date and have the average column ignore deleted entries and calculate the average only from entries containing a time.

I don't know how to do this.

Here's an example of my layout:

Columns AI to AR will contain timings in the format hh:mm:ss (the timings of a vehicle travelling between 2 points)

Column AS will show the average of all those timings taken - but must be able do so whilst ignoring nil entries (e.g. 00:00:00).

If anybody could explain how I can do this I would be most grateful.

 

[GlennUK]

An array formula ( entered using Ctrl-Shift-Enter ) of :

=AVERAGE(IF(AI2:AR2>0,AI2:AR2))

or, do:

SUM(AI2:AR2)/COUNTIF(AI2:AR2,">0")

 

[Shadowboxer]

Thanks for your help Glenn - got it working - much appreciated