# Excel Magic Trick 1117: Calculate Average For Each Sample: AVERAGEIFS, PivotTable, or OFFSET

17
62

Download Excel File:
See how to calculate an average for 12 different samples when numbers are in a single column:
1. (00:25 min) PivotTable (average in one condition/criteria)
2. (02:22 min) AVERAGEIFS Function (average in one condition/criteria)
3. (05:37 min) AVERAGE, ROWS and OFFSET functions (average formula that will use a dynamic range that points to the first three rows, then the next three, then the next three, etc.)

#### 17 COMMENTS

1. Hi there! Regarding part3. (05:37 min) AVERAGE, ROWS and OFFSET functions -> 1. Why do we put parenthesis in front of rows i.e =(rows(\$J\$5:J5)-1)*3+1 and not =rows((\$J\$5:J5)-1)*3+1. Can you please explain?

2. hey very useleful video, for the offset method, can the reference point be the first point of the range?

3. This is excellent. Thank you. How does a pivot handle text that appears in a column when is its calculating an average?

4. One other option comes to mind, using the SUMPRODUCT function:

=ROUND(SUMPRODUCT(–(Sample=S5),Number)/SUMPRODUCT(–(Sample=S5)),1), where S5 contains the sample values: 1,2,3,… .

5. Excellsfun.. Is it possible to send you a PM if I have a question about an excel problem I've got?
I can't figure it out. Not even by watching your vids, even though I came close.

6. Excel Magic Trick 1117: Calculate Average For Each Sample: AVERAGEIFS, PivotTable, or OFFSET

Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm
See how to calculate an average for 12 different samples when numbers are in a single column:
1. (00:25 min) PivotTable (average in one condition/criteria)
2. (02:22 min) AVERAGEIFS Function (average in one condition/criteria)
3. (05:37 min) AVERAGE, ROWS and OFFSET functions (average formula that will use a dynamic range that points to the first three rows, then the next three, then the next three, etc.)