Excel Magic Trick 1198


Median for Each Quarter Using AGGREGATE Function

DESCRIPTION

See how to calculate Median for Each Quarter Using AGGREGATE Function
(00:08) Problem Setup
(00:49) Defined Names for ranges using keyboard for Create Names From Selection: Ctrl + Shift + F3
(01:54) Apply Names in Formula
(02:14) Create Upper and Lower Dates Limits for each quarter
(02:23) EOMONTH function to get End of Each Quarter
(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.

WORKBOOKS







Related Articles

Excel Is Fun

Excel Magic Trick 1244

DESCRIPTION Transform GDP Data Set: Advanced Filter with Formula Criteria wins! From Bill Szysz at YouTube, see how to use a Formula in Criteria area of Advanced Filter to extract data based on three conditions [Read More >>>]

Excel Is Fun

Excel Magic Trick 1104

DESCRIPTION See how to add unit numbers with AND and OR Criteria where the criteria are given as years and months but date column contains serial number dates. See how to use SUMIFS function to [Read More >>>]

Excel Is Fun

Excel Magic Trick 1477

DESCRIPTION In this video learn how to use the COUNTIFS and AND Functions to create a Logical Formula that will deliver a TRUE when the Event Key is a Duplicate AND the Task Column equals [Read More >>>]

Be the first to comment

Leave a Reply