# Excel question, filter + average **URL:** \ **Category:** Factual Questions\ **Created:** [November 13, 2009, 12:19am UTC](https://boards.straightdope.com/t/excel-question-filter-average/517388 "2009-11-13T00:19:34Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![NoCoolUserName](https://avatars.discourse-cdn.com/v4/letter/n/5fc32e/32.png) [@NoCoolUserName](https://boards.straightdope.com/u/NoCoolUserName)\ **Post date:** [November 13, 2009, 12:19am UTC](https://boards.straightdope.com/t/excel-question-filter-average/517388/1 "2009-11-13T00:19:34Z")
This is probably easy, but I don’t know how to do it. I have a spreadsheet that lists the sales from my little bookstore. I have a field to keep track of where they sell (Alibris, Amazon, eBay, etc.). How do I filter by that field and show an average of what the books sell for in each venue? Field 1: sale price Field 2: fees Field 3: postage charged Field 4: postage spent Field 5: cost Field 6: net (calculated: 1 -2 +3 -4 -5) Field 7: venue I want to show the average of Field 6 for each value in Field 7. MicroSoft Excel 2007 ---
**Author:** ![notfrommensa](https://avatars.discourse-cdn.com/v4/letter/n/f14d63/32.png) [@notfrommensa](https://boards.straightdope.com/u/notfrommensa)\ **Post date:** [November 13, 2009, 12:30am UTC](https://boards.straightdope.com/t/excel-question-filter-average/517388/2 "2009-11-13T00:30:30Z")
Check out _**=averageif(range,criteria,average\_range)**_ ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [November 13, 2009, 12:45am UTC](https://boards.straightdope.com/t/excel-question-filter-average/517388/3 "2009-11-13T00:45:52Z")
> [@NoCoolUserName](#): > > I want to show the average of Field 6 for each value in Field 7. You can use **notfrommensa** ’s solution if you know all of the possible values for Field7 ahead of time. For example, if Field7 only has 3 possible distinct values (Alibris, Amazon, eBay) then you copy that formula 3 times with 3 different criterias. If you don’t want to keep track of all possible values for Field7, you can use a Pivot Table. You drag Field7 into the header and it it will provide the groupings across all possible values. ---
**Author:** ![JWT\_Kottekoe](https://avatars.discourse-cdn.com/v4/letter/j/3ec8ea/32.png) [@JWT\_Kottekoe](https://boards.straightdope.com/u/JWT_Kottekoe)\ **Post date:** [November 13, 2009, 3:21am UTC](https://boards.straightdope.com/t/excel-question-filter-average/517388/4 "2009-11-13T03:21:28Z")
> [@Ruminator](#): > > … use a Pivot Table… What he said! Pivot tables are confusing at first, but once you get the hang of it, they are very powerful and ideal for the task you described. ---
**Author:** ![muttrox](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@muttrox](https://boards.straightdope.com/u/muttrox)\ **Post date:** [November 13, 2009, 1:57pm UTC](https://boards.straightdope.com/t/excel-question-filter-average/517388/5 "2009-11-13T13:57:57Z")
Pivot tables are best. Another, simpler, alternative is subtotal. Sort it by venue, then have it put the average of net (field 6) after each change in venue (field 7). ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [November 13, 2009, 2:01pm UTC](https://boards.straightdope.com/t/excel-question-filter-average/517388/6 "2009-11-13T14:01:29Z")
> [@NoCoolUserName](#): > > Field 1: sale price > Field 2: fees > Field 3: postage charged > Field 4: postage spent > Field 5: cost > Field 6: net (calculated: 1 -2 +3 -4 -5) > Field 7: venue I vote pivot chart as well - it’s perfect for this. But a quick question: are your fields rows or columns? ---
**Author:** ![mbetter](https://avatars.discourse-cdn.com/v4/letter/m/53a042/32.png) [@mbetter](https://boards.straightdope.com/u/mbetter)\ **Post date:** [November 13, 2009, 3:46pm UTC](https://boards.straightdope.com/t/excel-question-filter-average/517388/7 "2009-11-13T15:46:52Z")
> [@muttrox](#): > > Pivot tables are best. > > Another, simpler, alternative is subtotal. Sort it by venue, then have it put the average of net (field 6) after each change in venue (field 7). It’s even simpler than that. SUBTOTAL() is filter-aware so you wouldn’t even have to sort it. Also, SUBTOTAL() can perform multiple operations other than SUM(), one of which is AVERAGE(). So, for net values in column F: =SUBTOTAL(1, F:F). The first parameter (1) indicates that the function will return the mean of the range indicated by the second parameter. Apply an Auto Filter to the data in questions and divide it up any way you want.