Best way to calculate multiple averages? (See sample mini-sheet)

[wrecclesham]

Book1
	B	C	D
2			Average
3	Apples	65
4	Apples	62
5	Apples	16	47.7
6	Oranges	17
7	Oranges	61	39.0
8	Peaches	17
9	Peaches	72
10	Peaches	24
11	Peaches	62	43.8
12	Tomatoes	96	96.0
Sheet1

Cell Formulas
Range	Formula
D5	D5	=AVERAGE(C3:C5)
D7	D7	=AVERAGE(C6:C7)
D11	D11	=AVERAGE(C8:C11)
D12	D12	=AVERAGE(C12)

I have a similar spreadsheet to the one above, containing several thousand rows of data.

How could I calculate the green column automatically?

 

[jasonb75]

You could try Data Subtotal

That would mean editing the range in every row, Dave. Which is what the OP was asking to avoid.

 

[Dave Patton]

"That would mean editing the range in every row, Dave. Which is what the OP was asking to avoid"

The Excel feature would not require any editing.

- select the range
- access Data Subtotal
- with each change in "Product" use option Average
the original data remains as well as the subtotals
choice 2 shows the subtotals

 

[jasonb75]

@Dave Patton
Your first reply assumed that whoever reads it is familiar with grouping (a feature that I rarely use, which is why I didn't follow what you meant to start with).
I would think it likely that anyone who uses grouping enough to understand what you were suggesting would have already thought to use it without the need to ask for suggestions.

 

[Dave Patton]

Thanks jasonb75

It is a feature on the main menu that some people have not noticed or tried.

If a person wants to average, sum, or ... data without entering formulas, they can try the feature that Microsoft provided.

 

[wrecclesham]

I think that I was a little too slow in removing the word 'simple' from the offer of making it dynamic, the formula is much the same as the one in my last reply but you won't need to edit it if you add more data to the source, just fill down as needed.

Whilst the formula looks more complicated, it uses simpler and more efficient methods to process the data. You may, or may not notice a difference in calculation time for the formula when compare to one using averageifs depending on a number of factors.

One thing that I should point out is that all of the suggestions provided assume that (for example) all Apples will be in consecutive rows, if they are separated then each group of Apples will have its own average.

=IF(B3=B4,"",LET(l,MATCH("zzz",B:B),r,C$2:INDEX(C:C,l),AVERAGE(C3:INDEX(r,IFERROR(MATCH(1E+100,D$2:D2)+1,1)))))

B3=B4 should refer to the item of interest (e.g. Apples) in the same row as the formula, and the row below.
B:B should refer to the entire column of items.
C$2 should refer to the cell immediately above the first value in the column of values to average.
C:C should refer to the entire column of values.
C3 should refer to the value cell in the same row as the formula.
D$2:D2 should refer to the cell above the one where you enter the first formula before filling down.

Hopefully that all makes sense.

Fortunately, they will be in consecutive rows.

The actual dataset is student grades, with each row being a different subject. We run an export from another system, which gives us all the grades for each student in consecutive rows. (The reason the number of rows varies is that students don't necessarily take the same number of courses.)

 

[jasonb75]

In that case, the formula that I suggested will work fine when applied to the full data set. Alternatively, there is the subtotal / grouping suggestion from @Dave Patton which only takes a couple of clicks to apply.

 

[wrecclesham]

Any idea how I can sort the data by the results of the Average column?

 

[jasonb75]

Which method are you using?

With @Dave Patton's suggested method, sorting is fairly simple. With the formulas suggested by @Flashbond and myself, sorting would need a totally different approach.

 

[wrecclesham]

Which method are you using?

With @Dave Patton's suggested method, sorting is fairly simple. With the formulas suggested by @Flashbond and myself, sorting would need a totally different approach.

I was trying the method you and @Flashbond suggested.

For some reason, I was having some trouble seeing how to apply Dave's method to my data. Maybe I need to sleep on it then take a second look.

 

[jasonb75]

I can re-write the formula method if necessary, but I think that Dave's suggestion will be much more practical although the layout of the results may not be exactly what you want.

Bear with me, I hadn't used this until Dave suggested it so I've had to figure it out as I go.

1. Starting with your mini example in post 1, enter "Item" and "Value" into B2 and C2 respectively (it doesn't work without column headers).
2. Select B2
3. On the Excel ribbon, go to the Data tab, then click Subtotal (on the far right).
4. In the pop up window, change the first dropdown to "Item" and the second to "Average". In the box below this, only "Value" should be checked.
5. The final 3 checkboxes at the bottom can be checked or unchecked, at this point it will make little to no difference.
6. Click the OK button and the subtotals will be added to the sheet (in the same columns as the original data rather than to the side.
7. To sort, look to the left of column A where you will now see columns 1, 2 and 3. Click column 2 to collapse all of the groups (using the [-] boxes will collapse a single group, clicking the column number will collapse all of them).
8. Click on the header in column C, then go back to the Data tab on the Excel ribbon and click on the Sort icon for ascending, or descending as desired.
9. Go back the the 3 columns to the right of column A and click column 3 to expand all of the groups in the sorted order.

If you would prefer the layout in your original example then repeat steps 2 and 3, this time click the "Remove All" button at the bottom of the popup to clear the subtotals, etc.
Depending on which formula you used it is possible that you may need to re-enter it. I tested with the LET formula and it gave an error in the last row after clearing the subtotals. Filling down the formula from the row above fixed this, however the formulas using AVERAGEIFS may behave differently.