Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return column header based on last value in row | Excel Worksheet Functions | |||
Returning Column Header based on Row and Value | New Users to Excel | |||
column data retrieved based on value in header row | Excel Discussion (Misc queries) | |||
pick from table based on row and column header | Excel Worksheet Functions | |||
calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) |