Average with Special Criteria

Thanks:  0
Likes:  0

# Thread: Average with Special Criteria

1. ## 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. ## 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. ## 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. ## Re: Average with Special Criteria

That works great!!

Thank you.

## User Tag List

#### Posting Permissions

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