Average cells based on column header
 

I have a long spreadsheet (columns A - HB). In column A5, I want to
average all of the entries on this row where the SCORE column is
higher than 0.00.

Example:
Ave-to-Date = A5

SCORE = F5, J5, N5, R5, ...HB5

The SCORE cells contain a formula [=SUM(C5-D5-E5)/((40*1-E5)*0.9)], so
they all read as "0.00" until that week's entry has been made. I only
want to average the cells that contain data, thus the 0.00 note.

Any help would be greatly appreciated.
Annabelle

Average cells based on column header
 

Annabelle presented the following explanation :
I have a long spreadsheet (columns A - HB). In column A5, I want to
average all of the entries on this row where the SCORE column is
higher than 0.00.

Example:
Ave-to-Date = A5

SCORE = F5, J5, N5, R5, ...HB5

The SCORE cells contain a formula [=SUM(C5-D5-E5)/((40*1-E5)*0.9)], so
they all read as "0.00" until that week's entry has been made. I only
want to average the cells that contain data, thus the 0.00 note.

Any help would be greatly appreciated.
Annabelle

In XL2007 you can use the AVERAGEIFS() function as follows:

In cell A5:
=AVERAGEIFS(F5:HB5,F1:HB1,"SCORE",F5:HB5,"0")

**Assumes each 'SCORE' column is labeled "SCORE" in Row1.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Average cells based on column header
 

On Apr 5, 12:03*pm, GS wrote:
Annabelle presented the following explanation :

I have a long spreadsheet (columns A - HB). In column A5, I want to
average all of the entries on this row where the SCORE column is
higher than 0.00.

Example:
Ave-to-Date = A5

SCORE = F5, J5, N5, R5, ...HB5

The SCORE cells contain a formula [=SUM(C5-D5-E5)/((40*1-E5)*0.9)], so
they all read as "0.00" until that week's entry has been made. I only
want to average the cells that contain data, thus the 0.00 note.

Any help would be greatly appreciated.
Annabelle

In XL2007 you can use the AVERAGEIFS() function as follows:

* In cell A5:
* =AVERAGEIFS(F5:HB5,F1:HB1,"SCORE",F5:HB5,"0")

* **Assumes each 'SCORE' column is labeled "SCORE" in Row1.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thank you, Garry. However, I'm still on 2003 (Fortune 50 company I
work for is a bit slow on office technology). Is there another
function that might work for my older Excel version?

Average cells based on column header
 

Annabelle formulated on Tuesday :
Thank you, Garry. However, I'm still on 2003 (Fortune 50 company I
work for is a bit slow on office technology). Is there another
function that might work for my older Excel version?

Do you want a VBA custom function OR looking to go with built-in
functions? Note that the latter will require use of multiple functions.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Average cells based on column header
 

On Apr 5, 11:09*pm, GS wrote:
Annabelle formulated on Tuesday :

Thank you, Garry. However, I'm still on 2003 (Fortune 50 company I
work for is a bit slow on office technology). Is there another
function that might work for my older Excel version?

Do you want a VBA custom function OR looking to go with built-in
functions? Note that the latter will require use of multiple functions.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Custom functions are fine.

Average cells based on column header
 

Here's a quick-n-dirty function that accepts 6 different operators for
specifying criteria. (watch out for word wrap)

Public Function Average_2Ifs(RangeToAvg As Range, _
Criteria1Range As Range, Criteria1 As Variant, _
Criteria2Range As Range, Criteria2 As Variant) As
Double
' Returns the average of a range of values based on 2 specified
criteria.
' Criteria can be the same range or different ranges.

Dim sz As String, c As Range
Dim dValues As Double, iCount As Integer
Dim v1 As Variant, v2 As Variant

'Check combined operators first
If InStr(1, Criteria2, "<=", vbTextCompare) 0 Then sz = "<=": GoTo
GotIt
If InStr(1, Criteria2, "=", vbTextCompare) 0 Then sz = "=": GoTo
GotIt
If InStr(1, Criteria2, "<", vbTextCompare) 0 Then sz = "<": GoTo
GotIt
'If we got here then single operator used
If InStr(1, Criteria2, "<", vbTextCompare) 0 Then sz = "<": GoTo
GotIt
If InStr(1, Criteria2, "", vbTextCompare) 0 Then sz = "": GoTo
GotIt
If InStr(1, Criteria2, "=", vbTextCompare) 0 Then sz = "=": GoTo
GotIt

GotIt:
v2 = CDbl(Mid(Criteria2, Len(sz) + 1))
For Each c In Criteria1Range
If c.Value = Criteria1 Then
v1 = Cells(Criteria2Range.Row, c.Column).Value
Select Case sz
'Check combined operators first
Case "<=": If v1 <= v2 Then dValues = dValues + v1: iCount =
iCount + 1
Case "=": If v1 = v2 Then dValues = dValues + v1: iCount =
iCount + 1
Case "<": If v1 < v2 Then dValues = dValues + v1: iCount =
iCount + 1
'If we got here then single operator used
Case "<": If v1 < v2 Then dValues = dValues + v1: iCount =
iCount + 1
Case "": If v1 v2 Then dValues = dValues + v1: iCount =
iCount + 1
Case "=": If v1 = v2 Then dValues = dValues + v1: iCount =
iCount + 1
End Select
End If
Next
Average_2Ifs = (dValues / iCount)
End Function 'Average_2Ifs()

Example usage:
Formula to put in the target cell:

=average_2ifs($F$5:$HB$5,$F$1:$HB$1,"SCORE",$F$5:$ HB$5,"0")

**Assumes each 'SCORE' column is labeled "SCORE" in Row1.

I'm also working on a worksheet formula solution which I'll post later.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Average cells based on column header
 

On Apr 6, 12:24*pm, GS wrote:
Here's a quick-n-dirty function that accepts 6 different operators for
specifying criteria. (watch out for word wrap)

Public Function Average_2Ifs(RangeToAvg As Range, _
* * * * * * * * Criteria1Range As Range, Criteria1 As Variant, _
* * * * * * * * Criteria2Range As Range, Criteria2 As Variant) As
Double
' Returns the average of a range of values based on 2 specified
criteria.
' Criteria can be the same range or different ranges.

* Dim sz As String, c As Range
* Dim dValues As Double, iCount As Integer
* Dim v1 As Variant, v2 As Variant

* 'Check combined operators first
* If InStr(1, Criteria2, "<=", vbTextCompare) 0 Then sz = "<=": GoTo
GotIt
* If InStr(1, Criteria2, "=", vbTextCompare) 0 Then sz = "=": GoTo
GotIt
* If InStr(1, Criteria2, "<", vbTextCompare) 0 Then sz = "<": GoTo
GotIt
* 'If we got here then single operator used
* If InStr(1, Criteria2, "<", vbTextCompare) 0 Then sz = "<": GoTo
GotIt
* If InStr(1, Criteria2, "", vbTextCompare) 0 Then sz = "": GoTo
GotIt
* If InStr(1, Criteria2, "=", vbTextCompare) 0 Then sz = "=": GoTo
GotIt

GotIt:
* v2 = CDbl(Mid(Criteria2, Len(sz) + 1))
* For Each c In Criteria1Range
* * If c.Value = Criteria1 Then
* * * v1 = Cells(Criteria2Range.Row, c.Column).Value
* * * Select Case sz
* * * * 'Check combined operators first
* * * * Case "<=": If v1 <= v2 Then dValues = dValues + v1: iCount =
iCount + 1
* * * * Case "=": If v1 = v2 Then dValues = dValues + v1: iCount =
iCount + 1
* * * * Case "<": If v1 < v2 Then dValues = dValues + v1: iCount =
iCount + 1
* * * * 'If we got here then single operator used
* * * * Case "<": If v1 < v2 Then dValues = dValues + v1: iCount =
iCount + 1
* * * * Case "": If v1 v2 Then dValues = dValues + v1: iCount =
iCount + 1
* * * * Case "=": If v1 = v2 Then dValues = dValues + v1: iCount =
iCount + 1
* * * End Select
* * End If
* Next
* Average_2Ifs = (dValues / iCount)
End Function 'Average_2Ifs()

Example usage:
* Formula to put in the target cell:

* * =average_2ifs($F$5:$HB$5,$F$1:$HB$1,"SCORE",$F$5:$ HB$5,"0")

* * **Assumes each 'SCORE' column is labeled "SCORE" in Row1.

I'm also working on a worksheet formula solution which I'll post later.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thank you, Garry. I'll give this a try.