AVERAGEIFS Function in Excel: Average With Multiple Conditions
StatisticalLevel: IntermediateAvailable in: Excel 2007+AVERAGEIFS averages values where all conditions are true.Syntax=AVERAGEIFS(average_range.
Read article →Atul Vij
REDUCE Function in Excel: Boil an Array Down to One Result
REDUCE Function in Excel: Boil an Array Down to One Result
REDUCE is SCAN that returns only the final result — useful for repeated clean-up steps.
Read article →VBA Events: Macros That Run by Themselves (Worksheet_Change and More)
VBA Events: Macros That Run by Themselves (Worksheet_Change and More)
Excel VBA course lesson 12: Workbook_Open, Worksheet_Change, SelectionChange and BeforeSave events — timestamps when a cell is edited, auto-uppercase codes, validation, and how to…
Read article →Excel Lesson 9: Your First Charts
Excel Lesson 9: Your First Charts
Pick the right chart for the job, build column, line, pie and combo charts in seconds, add titles and data labels, change the chart…
Read article →VBA Worksheets and Workbooks: Add, Copy, Loop, Save Safely
VBA Worksheets and Workbooks: Add, Copy, Loop, Save Safely
Excel VBA course lesson 11: refer to workbooks and sheets without ActiveSheet bugs, loop through every sheet, add and rename sheets, copy sheets to…
Read article →DELTA Function in Excel: Are Two Numbers Equal?
DELTA Function in Excel: Are Two Numbers Equal?
DELTA returns 1 if two numbers are equal and 0 otherwise — easy to SUM for a count of matches.
Read article →PERCENTILE.EXC Function in Excel: Percentile Without the End Points
PERCENTILE.EXC Function in Excel: Percentile Without the End Points
PERCENTILE.EXC returns the k-th percentile but treats 0 and 1 as out of range. On small samples its results sit slightly wider than PERCENTILE.INC,…
Read article →VBA Subs and Functions: Reusable Code and Your Own Excel Formulas
VBA Subs and Functions: Reusable Code and Your Own Excel Formulas
Excel VBA course lesson 10: split a big macro into small Subs, pass arguments ByVal and ByRef, return values with Functions, and build user-defined…
Read article →VBA Arrays: Make Your Macros 100 Times Faster
VBA Arrays: Make Your Macros 100 Times Faster
Excel VBA course lesson 9: read a whole range into an array, work in memory, write it back once. Static and dynamic arrays, ReDim…
Read article →The Excel Camera Tool: A Live Picture of Any Range
The Excel Camera Tool: A Live Picture of Any Range
Excel’s hidden Camera tool puts a live, resizable picture of a range anywhere — perfect for one-page dashboards that mix tables of different column…
Read article →CUBERANKEDMEMBER Function in Excel: The n-th Member of a Set
CUBERANKEDMEMBER Function in Excel: The n-th Member of a Set
CUBERANKEDMEMBER returns the n-th member of a set, e.g. the 1st, 2nd and 3rd best customers from a CUBESET: the way to build Top-N…
Read article →SCAN Function in Excel: Running Totals in One Formula
SCAN Function in Excel: Running Totals in One Formula
SCAN walks through an array carrying an accumulator and returns every intermediate result — running totals, running balances, running max.
Read article →