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

Kir Ko

February 25, 2020

Really osm

Reply

K

February 25, 2020

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

Reply

tina:D

February 25, 2020

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?

Reply

Aman Kedia

February 25, 2020

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

Reply

amal hassnaoui

February 25, 2020

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

Reply

Cocojoa

February 25, 2020

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

Reply

Tanner Tucker

February 25, 2020

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

Reply

Mark Lang

February 25, 2020

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

Reply

Krayzie Roach

February 25, 2020

thnx 4 da info on pivot table averagez

Reply

ContentedSoul

February 25, 2020

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

Reply

Nicolai Tanghøj

February 25, 2020

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.

Reply

Eman A.

February 25, 2020

You are the best Mike.

Reply

Luis Omar

February 25, 2020

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

Reply

Bill Szysz

February 25, 2020

Great !!! :)))

Reply

Yoonunet Goor

February 25, 2020

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

Reply

Exploring Excel

February 25, 2020

Nice video. Many things to learn from you

Reply

ExcelIsFun

February 25, 2020

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

Reply

Leave a Reply