RE: Counting cells with a certain font colour

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.live.com/self.aspx/.Public/Excel%20Files/ColorAverage/colorAverage.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
.

Relevant Pages

  • Cyclo X ride report
    ... The Scottish cyclo cross circus came to town on Sunday, ... my star team-mate and Commonwealth games rider and we ... a lap of the field. ... Into the woods, we ...
    (uk.rec.cycling)
  • RE: Enetring data in an expanding table
    ... Dim ridersRange As Range ... Rider Lap 1 Lap2 ........ ... I need to select the cell to store data in based on rider number and then ...
    (microsoft.public.excel.misc)
  • Re: Olympics track events
    ... The Olympic or Team Sprint is more interesting. ... At the end of the second lap the second rider does the same ... which high ground was a lone freeway overpass. ...
    (rec.bicycles.racing)
  • RE: Counting cells with a certain font colour
    ... rider As Long) As Double ... Dim cell As Range ... Either of the two riders can do any lap in random order. ... lap times of rider1 in say blue font and the lap times of rider 2 in red ...
    (microsoft.public.excel.programming)
  • Help with program planning
    ... The program records the time the rider number is typed in as ... and each rider will have an ID tag that gets scanned at the end of each lap. ... The times at the end of lap 1, 2, etc are input using a user form ...
    (microsoft.public.excel.misc)