Average time for each individual range count.

[I need excel help]

Hello all,

I am wanting to get the macro I have in this sheet working.
The results I have in col in col G are averaging the times in col AM for each individual race.
I have resulted the first five races.

Col F counts how many there are for each race, col N is each different race. Cols B and D are dates and different tracks, there can be 3 x meetings on any given date.
Time format must remain the same. Otherwise, I cannot use the data if changed. 00:00.0

Thanks.

[josephteh]

Using formula, G2=AVERAGEIFS($AM$2:$AM$141,$B$2:$B$141,B2,$N$2:$N$141,N2), copy down and format the cells as mm:ss."00".

[Tajan]

Check this:

Please Login or Register  to view this content.

[I need excel help]

Hi, thanks for results.

My times contain the letter T, which means there is no time. I did not supply this data as a whole race, but now see it as a problem. Sorry for that.
Tajan,s macro works but doesn’t recognize if a whole race has the letter T as in cells AM9:AM16. I result this 00:00.0 . So, the macro stops every time when it encounters this.

I have seen when I have two or more meetings on the same date josephteh function is treating the one date as one meeting.
I changed the date to 8/01/2023 as an example.

Your macro and function are in version 2 sheet. You can see where things stop, and times are now incorrect.

I think the two fixes are to recognize letter T and that there can be more than one meeting on any given day. Col D separates more than one meeting.

Thanks.

[josephteh]

Add in one more criteria and enclose with IFERROR, =IFERROR(AVERAGEIFS($AM$2:$AM$141,$B$2:$B$141,B2,$N$2:$N$141,N2,$F$2:$F$141,F2),0)

[Tajan]

Change Macro to:

Please Login or Register  to view this content.

[I need excel help]

That fixed the problems, all working well now. Joseph, I changed your extra criteria to match the track and that worked.
Tajan, perfect fix.

Thanks both, solved.