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

Really osm

Can I get it for multiple orders with different quantity..?

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?

what to do if some samples aren't assigned any number at all? it shows #DIV/0!
in my case

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

so much helpful!!!!!!!!!!!! you saved me!!!!!!!!!!!

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

Dude! You saved my bacon at my work. First round of beers is on me.

thnx 4 da info on pivot table averagez

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

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.

You are the best Mike.

Great lessons!! awesome video!!! thank you!

Great !!! :)))

Great lesson. Thanks a lot . You're so useful.

Nice video. Many things to learn from you

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