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.
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.
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.