Excel Is Fun

Excel Magic Trick 1110

DESCRIPTION See how to compare two lists and extract records with an array formula that uses these functions: MATCH, ISNA, ISNUMBER, IF, ROWS, ROW, INDEX, AGGREGATE and a number of array operations. WORKBOOKS

Excel Is Fun

Excel Magic Trick 1109

DESCRIPTION See how to Compare Two Lists and Extract Records with Excel Advanced Filter: 1. Extract items in list 2 that are NOT in List 1 using Advanced Filter and Logical Formula in Criteria Area [Read More >>>]

Excel Is Fun

Excel Magic Trick 1108

DESCRIPTION See how to Compare Two Lists and Extract Records: 1. ISNA and MATCH function with Filter to extract items in list 2 that are NOT in List 1. 2. ISNUMBER and MATCH function with [Read More >>>]

Excel Is Fun

Excel Magic Trick 1107

DESCRIPTION See how to do a VLOOKUP to a different Sheet using: 1. Sheet References 2. Defined Names 3. Table Formula Nomenclature (Structured References) WORKBOOKS

Excel Is Fun

Excel Magic Trick 1106

DESCRIPTION See how to add 3-D gradient conditional formatting to a row in a data set using the AND function to match student name and academic quarter. WORKBOOKS

Excel Is Fun

Excel Magic Trick 1105

DESCRIPTION See how and why to use the DMIN function for calculating the minimum value with two conditions/criteria. Rules for D Functions: We must have: 1) Proper data Set w/ Field Names 2) We can [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 1103

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 [Read More >>>]

Excel Is Fun

Excel Magic Trick 1102

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

Excel Is Fun

Excel Magic Trick 1101

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 [Read More >>>]