| Home |
| Search |
| Today's Posts |
|
|
|
#1
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) | |||
![Annabelle[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
