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

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

[SabbirRahmans]

You just saved10 hours for me. God bless you brother!

[excelisfun]

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

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

[footloosewith_alice]

THANK YOU THANK YOU AND THANK YOU for this video!!! I searched for this for soo long and finally found the most relevant video

[marcusambrose]

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

[ExploringExcelAmey]

Nice video. Many things to learn from you

[yoonunetgoor]

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

[Cuberbill]

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

[ContentedSoul]

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

[KrayzieRoach]

thnx 4 da info on pivot table averagez

[amalhassnaoui]

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

[tannertucker]

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

[tuancassim]

how to get the average on this excel sheet attached

[SpartanForever]

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

[Seftehandle]

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?

[amankedia]

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

[ronitbro]

83.84

74.19

55.81

43.97

42.9

27.05

Sir, I have above 6 numbers only in column or row, but I need only those three numbers out of above 6 numbers that give me average of 50 or nearby 50 (+5 or -5) I want those three numbers, highlighted or separated automatically.

[hundikaj]

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.