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/

## Kir Ko

February 25, 2020Really osm

## K

February 25, 2020Can I get it for multiple orders with different quantity..?

## tina:D

February 25, 2020Hi 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?

## Aman Kedia

February 25, 2020what to do if some samples aren't assigned any number at all? it shows #DIV/0!

in my case

## amal hassnaoui

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

## Cocojoa

February 25, 2020so much helpful!!!!!!!!!!!! you saved me!!!!!!!!!!!

## Tanner Tucker

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

## Mark Lang

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

## Krayzie Roach

February 25, 2020thnx 4 da info on pivot table averagez

## ContentedSoul

February 25, 2020One 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,… .

## Nicolai Tanghøj

February 25, 2020Excellsfun.. 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.

## Eman A.

February 25, 2020You are the best Mike.

## Luis Omar

February 25, 2020Great lessons!! awesome video!!! thank you!

## Bill Szysz

February 25, 2020Great !!! :)))

## Yoonunet Goor

February 25, 2020Great lesson. Thanks a lot . You're so useful.

## Exploring Excel

February 25, 2020Nice video. Many things to learn from you

## ExcelIsFun

February 25, 2020Excel 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.)