Excel question, filter + average

General Questions
#1

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

#2

Check out =averageif(range,criteria,average_range)

#3

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.

#4

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.

#5

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

#6

I vote pivot chart as well - it’s perfect for this. But a quick question: are your fields rows or columns?

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