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

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

On Apr 5, 12:03*pm, GS <g...@somewhere.net> 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?

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

On Apr 5, 11:09*pm, GS <g...@somewhere.net> 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.

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

On Apr 6, 12:24*pm, GS <g...@somewhere.net> 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.