Counting cells with a certain font colour
 

I have a row of lap times which represents the lap times of the two
competitors in a team. The competitors do not have to do alternative laps.
Either of the two riders can do any lap in random order. My aim is to
identify which rider did each lap and then give an average lap time for each
rider based on the laps they completed.


Each rider in the team is identified by an indivual tag that is scanned on
the completion of each lap so it is easy to identify which rider did each lap.

This is my thoughts on how to do it. Using code I was going to record the
lap times of rider1 in say blue font and the lap times of rider 2 in red
font. This immediately gives a visual cue to the riders who did what laps.
Then I was going to count and add the cells of each font colour to calculate
the average.

Based on my searches of this forum I think this could be done but can anyone
think of an easier way.

Below is an example of a typical team result setout where Av is the average
and R1 represents a lap time by rider 1 and R2 by rider2.


R1Av R2Av R1 R1 R2 R1 R1 R1
R2 R1 R2

Counting cells with a certain font colour
 

In case it is important there would be around 100 teams and a maximum of
around 20 laps completed by any one team. The races are based on a 4 hour
time duration not set number of laps so the number of laps is unknown at the
start of each race.

"NDBC" wrote:

I have a row of lap times which represents the lap times of the two
competitors in a team. The competitors do not have to do alternative laps.
Either of the two riders can do any lap in random order. My aim is to
identify which rider did each lap and then give an average lap time for each
rider based on the laps they completed.


Each rider in the team is identified by an indivual tag that is scanned on
the completion of each lap so it is easy to identify which rider did each lap.

This is my thoughts on how to do it. Using code I was going to record the
lap times of rider1 in say blue font and the lap times of rider 2 in red
font. This immediately gives a visual cue to the riders who did what laps.
Then I was going to count and add the cells of each font colour to calculate
the average.

Based on my searches of this forum I think this could be done but can anyone
think of an easier way.

Below is an example of a typical team result setout where Av is the average
and R1 represents a lap time by rider 1 and R2 by rider2.


R1Av R2Av R1 R1 R2 R1 R1 R1
R2 R1 R2

Counting cells with a certain font colour
 

Its quite easy to do your way, and as you said, its quite visually obvious.

to start color a number of cells , and range name them rider1, rider2 and so
on

add this code to a standard code module, its a UDF (iser defined function)

Option Explicit

Function GetAverage(source As Range, rider As Long) As Double
Dim clr As Long
Dim total As Long
Dim count As Long
Dim cell As Range
clr = Range("rider" & rider).Interior.Color
Application.Volatile
For Each cell In source.Cells
If cell.Interior.Color = clr Then
count = count + 1
total = total + cell.Value
End If
Next

If count 0 Then
GetAverage = total / count
Else
GetAverage = 0
End If
End Function


in the worksheet, say cells C6 to K6 i have colred and added values
in B6 put this
=GetAverage(C6:K6,1)

this says check each cell in the selected range and give me the average for
rider #1
In A6 put this
=GetAverage(C6:K6,2)
which references the same row of times, but returns the average for rider #2

its versatile - you can change colors and add riders without having to amend
to code


file:
http://cid-b8e56c9a5f311cb7.skydrive...lorAverage.xls

"NDBC" wrote:

I have a row of lap times which represents the lap times of the two
competitors in a team. The competitors do not have to do alternative laps.
Either of the two riders can do any lap in random order. My aim is to
identify which rider did each lap and then give an average lap time for each
rider based on the laps they completed.


Each rider in the team is identified by an indivual tag that is scanned on
the completion of each lap so it is easy to identify which rider did each lap.

This is my thoughts on how to do it. Using code I was going to record the
lap times of rider1 in say blue font and the lap times of rider 2 in red
font. This immediately gives a visual cue to the riders who did what laps.
Then I was going to count and add the cells of each font colour to calculate
the average.

Based on my searches of this forum I think this could be done but can anyone
think of an easier way.

Below is an example of a typical team result setout where Av is the average
and R1 represents a lap time by rider 1 and R2 by rider2.


R1Av R2Av R1 R1 R2 R1 R1 R1
R2 R1 R2

Counting cells with a certain font colour
 

i used cell color instead of font color

but this is an easy change
replace
..Interior.Color
by
..Font.Color



"Patrick Molloy" wrote:

Its quite easy to do your way, and as you said, its quite visually obvious.

to start color a number of cells , and range name them rider1, rider2 and so
on

add this code to a standard code module, its a UDF (iser defined function)

Option Explicit

Function GetAverage(source As Range, rider As Long) As Double
Dim clr As Long
Dim total As Long
Dim count As Long
Dim cell As Range
clr = Range("rider" & rider).Interior.Color
Application.Volatile
For Each cell In source.Cells
If cell.Interior.Color = clr Then
count = count + 1
total = total + cell.Value
End If
Next

If count 0 Then
GetAverage = total / count
Else
GetAverage = 0
End If
End Function


in the worksheet, say cells C6 to K6 i have colred and added values
in B6 put this
=GetAverage(C6:K6,1)

this says check each cell in the selected range and give me the average for
rider #1
In A6 put this
=GetAverage(C6:K6,2)
which references the same row of times, but returns the average for rider #2

its versatile - you can change colors and add riders without having to amend
to code


file:
http://cid-b8e56c9a5f311cb7.skydrive...lorAverage.xls

"NDBC" wrote:

I have a row of lap times which represents the lap times of the two
competitors in a team. The competitors do not have to do alternative laps.
Either of the two riders can do any lap in random order. My aim is to
identify which rider did each lap and then give an average lap time for each
rider based on the laps they completed.


Each rider in the team is identified by an indivual tag that is scanned on
the completion of each lap so it is easy to identify which rider did each lap.

This is my thoughts on how to do it. Using code I was going to record the
lap times of rider1 in say blue font and the lap times of rider 2 in red
font. This immediately gives a visual cue to the riders who did what laps.
Then I was going to count and add the cells of each font colour to calculate
the average.

Based on my searches of this forum I think this could be done but can anyone
think of an easier way.

Below is an example of a typical team result setout where Av is the average
and R1 represents a lap time by rider 1 and R2 by rider2.


R1Av R2Av R1 R1 R2 R1 R1 R1
R2 R1 R2