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

17
5



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

Nguồn:https://dantrithegioi.com/

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here