Want to create an advanced filter that averages the values of a unique column.

[Wamhoi]

I wish to create an advanced filter that averages the values of a unique column. For example. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Column A has the following A,B,C,C,A in consecutive rows without blanks<o></o>
Column B respectively has the values 1,2,3,6,2<o></o>
<o></o>
After the Advanced Filter I would Like the following. <o></o>
<o></o>
Column A (Unique Filter): A,B,C<o></o>
Column B (Average of Values per Unique Filter): 1.5,2,4.5<o></o>
<o></o>
I know pivot tables does it better but I want I'd rather do it in an advanced filter so I can add other instructions. <o></o>
<o></o>
Thanks! <o></o>

 

[mikerickson]

DAVERAGE uses the same kind of criteria ranges that AdvancedFilter does.

 

[Wamhoi]

Hi mikerickson,

Thanks for the reply but it seems to only work correctly for the first cell. The forumla I used was =DAVERAGE(Data,5,$A$1:A2). As I drag the forumula downard the respective change would result in =DAVERAGE(Data,5,$A$1:A3) and so on.

If we take the resulting value in B3 ("B" being the column the formula is situated), the "Daverage" would averages all the values attributed to A2 and A3 where I only want each individual one.

So for example X should only have the averages for x and Y should only have the averages for Y. Is there any way to do that?

I hope that makes sense.

Thanks.

 

[mikerickson]

One of the drawbacks of DataBase functions is that most criteria ranges don't drag well, since they need at least two rows.

 

[Wamhoi]

Thanks again for your help. I ended up taking a Sumif and dividing it by a countif and it worked.