Excel Magic Trick 1198: Median for Each Quarter Using AGGREGATE Function

preview_player
Показать описание

See how to calculate Median for Each Quarter Using AGGREGATE Function
1. (00:08) Problem Setup
2. (00:49) Defined Names for ranges using keyboard for Create Names From Selection: Ctrl + Shift + F3
3. (01:54) Apply Names in Formula
4. (02:14) Create Upper and Lower Dates Limits for each quarter
5. (02:23) EOMONTH function to get End of Each Quarter
6. (03:19) AGGREGATE function to calculate Median with two criteria: Lower Date and Upper Date fo5 each Quarter. Using Function 17: Quartile.INC and a Boolean AND Logical Test (AND Criteria) using Multiplication.
  1. excelisfun
  2. Excel
  3. Microsoft Excel
  4. Highline College
  5. Mike Girvin
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:08:32

Excel Magic Trick 1198: Median for Each Quarter Using AGGREGATE Function

Excel Magic Trick 0:08:00

Excel Magic Trick 1179: Calculate Mean, Median and Mode From Data Set

Excel Magic Trick 0:09:21

Excel Magic Trick 1090: MEDIAN Function 3 Amazing Uses: OR Criteria, Payroll, Commissions

Excel Magic Trick 0:16:10

Excel Magic Trick 1101: Calculate MEDIAN Based On Lower/Upper Date, Exclude Holidays & Weekends

Excel Magic Trick 0:05:56

Excel Magic Trick 1035: Calculate Median with 1 condition with AGGREGATE & PERCENTILE.INC functi...

Excel Magic Trick 0:05:08

Excel Magic Trick 1189: Extract Color From Description Create Revenue by Color PivotTable, 2nd Take

How to Use 0:00:33

How to Use Median Function in Excel

Excel Magic Trick 0:09:09

Excel Magic Trick 1203: 2 Slicers Control 4 PivotTables: Sum, Running Total, Change and % Change

Excel Magic Trick 0:10:07

Excel Magic Trick 1201: Conditional Format Product With No Sales (5 Examples)

Excel Magic Trick 0:04:06

Excel Magic Trick 1202: Conditional Formatting For a Cross Tabulated PivotTable

Excel - use 0:05:47

Excel - use the Median function in a PivotTable by Chris Menard

Excel Magic Trick 0:06:37

Excel Magic Trick 1199: Formula to Extract Everything After Last Space: Two Formula Solutions

Excel Magic Trick 0:09:53

Excel Magic Trick 1175: Decaying Average in Standards-Based Grading in Excel

Excel Magic Trick 0:10:33

Excel Magic Trick 1192: Match Lookup Value to Closest Number in 1st Column of Lookup Table

How to use 0:02:17

How to use the Median Function in Excel

Excel Magic Trick 0:07:45

Excel Magic Trick 1195: Are YYMMDD Text Dates 1900 or 2000? Formula or Text To Columns Solution?

Excel calculate the 0:10:53

Excel calculate the Median IF - two methods | Function and PivotTable

How to Find 0:00:56

How to Find the Median of Sales for a Specific Month in Excel

Median and Mode 0:09:25

Median and Mode Using Multiple Conditions in Excel

Excel Magic Trick 0:12:52

Excel Magic Trick 1207: PERCENTILE.EXC, PERCENTILE.INC, QUARTILE.EXC, QUARTILE.INC Functions

MEDIAN Excel Function 0:11:20

MEDIAN Excel Function | How to Calculate Median in Excel?

Excel Median Formula 0:03:48

Excel Median Formula with Example

Excel Lab 5 0:04:47

Excel Lab 5 Conditional Functions, Average versus Median

Single Cell Spilled 0:24:50

Single Cell Spilled Reports with Dynamic Total Row & Formatting. Excel Magic Trick 1703.

Комментарии
Автор

Excel Magic Trick 1198: Median for Each Quarter Using AGGREGATE Function 
See how to calculate Median for Each Quarter Using AGGREGATE Function
1. (00:08) Problem Setup
2. (00:49) Defined Names for ranges using keyboard for Create Names From Selection: Ctrl + Shift + F3
3. (01:54) Apply Names in Formula
4. (02:14) Create Upper and Lower Dates Limits for each quarter
5. (02:23) EOMONTH function to get End of Each Quarter
6. (03:19) AGGREGATE function to calculate Median with two criteria: Lower Date and Upper Date for each Quarter. Using Function 17: Quartile.INC and a Boolean AND Logical Test (AND Criteria) using Multiplication.

excelisfun
Автор

Thanks Mike. Another great example of what aggregate can do and brilliantly explained by you.

krn
Автор

Great! Besides AGGREGATE, I liked the use of names and the name box for navigation! :)

pmsocho
Автор

Fantastic AGGREGATE example !!!

Many, mamy thanks for your effort to propagate excel knowledge (with fun) :-))

BillSzysz
Автор

Thank you for the helpful video! What do I do if I have blank cells in my range? How do I get Excel to ignore the blank cells? Thank you in advance!

jessicak
Автор

What is the reason we have to divide Units by the other array?

pipo
Автор

Hey Mike.... I am a huge fan of yours 🙂

I have multiple tables with 2 columns named ENTERING and EXITING with multiple rows of start and end dates in it. What I want is to find out a common date range that falls in all tables.

Can you help Mike?

kuldeepsinghtanwar
Автор

Hello,

Could you please help me, I have many excel files with same data headers I have to merge all the excel files into the one master file or workbook.
Thanks in advance.

jackkaran
Автор

Hi Can you share the Media(If) version of the solution as well? much appreciated.

ambikesh
Автор

What did the Apply Define Names do? I didn't see anything change when you did that to the Min and Max dates.

Sal_A
Автор

Hey Mike,

By that array formula you meant something like this?

{=MEDIAN(IF(((--(D5<=Date))*(--(E5>=Date)))>0, Units))}

Getting the same results as you do! The formula looks pretty ugly though. D:

MrSarky