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

Dueling Excel

Dueling Excel 125

DESCRIPTION Conditional Formatting Date Four Days Old & Not Paid See Mr Excel and excelisfun use two methods to Conditional Formatting Date That is Four Or More Days Old & Not Paid 1. Conditional Formatting [Read More >>>]

Excel Is Fun

Excel Magic Trick 1201

Conditional Format Product With No Sales (5 Examples) DESCRIPTION See how to: (00:08) Problem Setup (00:43) Format Product with NO Sales: COUNT & =0 (02:46) Format Product with NO Sales: COUNT & NOT (Any non-zero [Read More >>>]

Excel Is Fun

Excel Magic Trick 1452

DESCRIPTION See two methods: 1) PivotTable Method, 2) Array Formula Method. The PivotTable Method is the easy way to accomplish this. The Array Formula method is a difficult Array Formula, but it can make all [Read More >>>]

Be the first to comment

Leave a Reply