Average with Special Criteria

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Average with Special Criteria

  1. #1

    Join Date
    Sep 2008
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Average with Special Criteria

     
    I have a spreadsheet in which I am trying to Average rows by searching for a partial criteria found in Column A. For example, if A1 contains "con.", "Dom.", or "GBL.", then Average the row in H1. If A1 contains "SWL.", then average that row in I1.

    This is how the spreadsheet looks:
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    XXX.XXX.=DOM.XX
    7.00
    5.00
    1.00
    6.00
    2.00
    3.98
    4.16

    2
    XXX.XXX.=CON.XXX
    1.00
    3.00
    4.00
    0.00
    3.00
    9.00
    3.33

    3
    XXX.XXX.=GBL.XXX
    2.00
    7.00
    0.00
    8.00
    0.00
    0.00
    2.83

    4
    XXXX.XX.=SWL.XXX
    10
    0
    60
    0
    0
    50

    120
    5
    XXX.XX.XXX.=SWL.XXX
    0
    15
    0
    0
    0
    0

    15


    This is the formula I have so far:
    Code:
    Dim arrRes,  
    I as Interger
    Rng as Range
     
    arrRes = array(“Dom.”,”GBL.”,”Con.”)
    For Each rng In Range("A17", Range("A17").End(xlDown))
    For i = 0 To UBound(arrComp)
    If InStr(rng, arrRes(i)) > 0 Then
    arrRes(i) = Application.WorksheetFunction.Average(rng.Offset(l - 1).Resize(1, 1)
     
    Next (i)

  2. #2
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average with Special Criteria

    Your explanation is not clear and also the code doesn't make sense.

    Can you show us the result you expect from your sample data and the reason ?

  3. #3

    Join Date
    Feb 2003
    Location
    Gurgaon/Thrissur
    Posts
    2,615
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average with Special Criteria

    Hi,

    In H1 and copied down,

    =IF(ISNA(LOOKUP(9.9999E+307,SEARCH({"con.","dom.","gbl."},A1))),"",AVERAGE(B1:G1))

    In I1 and copied down,

    =IF(ISNUMBER(SEARCH("swl.",A1)),AVERAGE(B1:G1),"")

    HTH

  4. #4

    Join Date
    Sep 2008
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average with Special Criteria

      


    That works great!!

    Thank you.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com