RE: Counting cells with a certain font colour
- From: Patrick Molloy <PatrickMolloy@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 2 Sep 2009 08:02:01 -0700
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.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
- References:
- Counting cells with a certain font colour
- From: NDBC
- RE: Counting cells with a certain font colour
- From: Patrick Molloy
- Counting cells with a certain font colour
- Prev by Date: RE: How do I add Icon to Toolbar?
- Next by Date: Re: How do I add Icon to Toolbar?
- Previous by thread: RE: Counting cells with a certain font colour
- Next by thread: Re: How run mp3 audio files in vba
- Index(es):
Relevant Pages
|
