Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel data sheet with lots and lots of rows that looks something like

C1    C2    Value
---   ---   -----
 A     C    0.228
 B     D    0.234
 A     D    0.359
 A     C    0.125
...

I want to find the average of Value where C1 = A and C2 = D , what's the best way to go about doing this? I know about the AVERAGEIF and COUNTIF and SUMIF functions, but they don't seem to work with multiple conditions...

share|improve this question
    
I would suggest providing a formula you have tried and maybe some one can point out where the problem lies. –  Darius Aug 25 '11 at 0:28
add comment

2 Answers

up vote 4 down vote accepted

In Excel 2007 and later you can use the AVERAGEIFS function, like so:

=AVERAGEIFS(C2:C5, A2:A5, "A", B2:B5, "D")
share|improve this answer
add comment

You could always use the inbuilt VBA language. I just tried this and it works - YMMV.

Go to Tools | Macro | Visual Basic Editor.

A new window will open.

Go to Tools | References. Click the checkbox beside Select Microsoft ActiveX Data Objects 2.5 Library. Click Ok.

Double click Sheet1 from the list over to the left. (The Project Explorer) - your sheet may be named something differently.

Copy and paste the following code into the code window (the window with the two dropdown lists at the top)

See the highlighted line below - edit it to make sure you define the full path to your Excel workbook. ie:

sXLSFile = "s:\brad\book1.xls"

Click the Run menu, then click the Run Sub/Userform (F5) option.

All things being equal (!), you should get a message box pop up with the average of the selected rows - see the 2nd highlighted line below which actually runs the query.

.Open "**select avg(value) from [sheet1$] where c1='A' and c2='D'**"

You just need to change the above line to change the query - eg if you wanted the average where C1 = "B" and C2 = "A"

Sub Main()

    Dim sXLSFile As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    ' set the location of the Excel worksheet
    **sXLSFile = "s:\brad\book1.xls"**
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & sXLSFile & ";Extended Properties=Excel 8.0;"
        .Open
    End With

    If Not cn Is Nothing Then
        Set rs = New ADODB.Recordset
        With rs
            Set .ActiveConnection = cn
            .Open "**select avg(value) from [sheet1$] where c1='A' and c2='D'**"
            MsgBox "The average is: " & rs(0)
        End With

    End If

    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then
            rs.Close
        End If
    End If
    Set rs = Nothing

    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then
            cn.Close
        End If
    End If
    Set cn = Nothing

End Sub

Hope that helps!

Reference: http://support.microsoft.com/kb/257819

share|improve this answer
    
This is why more schools should teach Basic. Everyone friggin uses Office. . . –  surfasb Aug 25 '11 at 1:34
    
@surfasb And everyone friggin uses Office on Windows in an environment that doesn't think that macros are dangerous ;-) –  digitxp Aug 25 '11 at 1:35
    
@digitxp - lol! but you're right. :) –  bradzo Aug 25 '11 at 2:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.