### Excel Magic Trick 1103

March 1, 2015

DESCRIPTION Formulas for Quarters, Fiscal Quarters & Fiscal Years: (00:07) Quarters using CEILING and MONTH function (02:48) Fiscal Quarter using LOOKUP and MONTH (05:24) Fiscal Quarter using IF, MONTH and CEILING (06:40) Fiscal Year using […]

### Excel Magic Trick 1102

February 28, 2015

DESCRIPTION See how to string three VLOOKUPs and IFERROR together to do a lookup from three tables in order to rank movies. WORKBOOKS

### Excel Magic Trick 1101

February 27, 2015

DESCRIPTION See two formulas: (04:00) MEDIAN Lower/Upper Date, Exclude Holidays: MEDAIN with IF, ISNA and MATCH (08:29) MEDIAN Lower/Upper Date, Exclude Holidays: AGGREGATE, ISNA and MATCH and Array Calculations (12:40) MEDIAN Lower/Upper Date, Exclude Holidays […]

### Excel Magic Trick 1100

February 7, 2015

DESCRIPTION See Two Methods that can return a “Yes” when a Two Way Lookup based on a Column Header and Row Header must lookup items from a second table that presents Dimension (row / column […]

### Excel Magic Trick 1099

February 6, 2015

DESCRIPTION See how to compare two e-mail lists where one list has extra semi-colons and we want a dynamic formula that can handle periodic data dumps. (00:08) Setup of problem (00:46) Excel Table Feature to […]

### Excel Magic Trick 1098

February 5, 2015

DESCRIPTION See how to compare two e-mail lists where one list has extra semi-colons: (00:28) Find and Replace feature to remove semi-colons (Ctrl + H). (01:32) Is in List: MATCH and ISNUMBER functions. (03:20) Is […]

### Excel Magic Trick 1097

February 4, 2015

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 […]

### Excel Magic Trick 1096

February 3, 2015

DESCRIPTION See how to extract date from middle of text string with a formula that uses SUBSTITUTE, SEARCH and MID functions. WORKBOOKS

### Excel Magic Trick 1095

February 2, 2015

DESCRIPTION See how to count Doubles, triples, quadruples, etc. using FREQUENCY function rather than COUNTIF. FREQUENCY is much faster calculating. WORKBOOKS

### Excel Magic Trick 1094

February 1, 2015

DESCRIPTION See how to lookup all A8 cell references from across many sheets using a wildcards, incomplete sheet names and the INDIRECT function. WORKBOOKS