Excel Magic Trick 1198

March 28, 2016 0


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 1137

June 25, 2015 0

Excel & PowerPivot DAX Formula: Approximate Match Lookup Without VLOOKUP DESCRIPTION See how to (00:08) Intro. (01:21) Approximate Match with VLOOKUP. (02:20) Approximate Match with AGGREGATE (to simulate logic that is present in DAX formula). […]

Excel Is Fun

Excel Magic Trick 1097

February 4, 2015 0

DESCRIPTION See how to: (00:11) AND and OR Criteria. (01:14) Counting Formula with SUMPRODUCT and COUNTIFS and a function argument array operation (03:16) Adding Formula with SUMPRODUCT and SUMIFS and a function argument array operation […]

Dueling Excel

Dueling Excel 018

March 18, 2015 0

DESCRIPTION See Mr Excel and excelisfun use the functions: IF, FIND, SEARCH, AND, OR, ISNUMBER, ISERROR, REPLCE and VLOOKUP. See Mr Excel and excel is fun create an IF function formula that use the functions […]

Be the first to comment

Leave a Reply