Excel Dynamic Arrays 2nd Edition by Bill Jelen ISBN 1615472657 9781615472659

Excel Dynamic Arrays 2nd Edition by Bill Jelen – Ebook PDF Instant Download/Delivery: 1615472657, 9781615472659
Full download Excel Dynamic Arrays 2nd Edition after payment

Product details:

ISBN 10: 1615472657 
ISBN 13: 9781615472659
Author: Bill Jelen

Excel Dynamic Arrays 2nd Table of contents:

1 – Getting Started
formulas can now spill
what happens if a formula can’t spill?
if your formula points to a table, the array will expand
what is really happening behind the scenes?
using the new array reference notation: E3#
what about implicit intersection?

2 – The SORT Function
a simple sort with one argument
sorting with a single argument
a sort based on two or more columns of results
sort by column
a random sort using SORT and RANDARRAY
what’s left for Ctrl+Shift+Enter?

3 – The SORTBY Function
a sort by something that is not in the results
performing a multi-column sort without array constants

4 – The FILTER Function
using the FILTER function with one condition
using FILTER with multiple conditions

5 – The UNIQUE Function
syntax of the UNIQUE function
understanding unique versus distinct

6 – Combining Functions
nesting array functions: SORT and UNIQUE
nesting array functions: SORT, UNIQUE, and FILTER

7 – The SEQUENCE Function
generating a range of sequential numbers
using SEQUENCE inside another function

8 – The RANDARRAY Function
generating an array of random numbers with RANDARRAY
using RANDARRAY for modeling and simulation

9 – Why CSE Arrays Were So Hard: Implicit Intersection
why CSE formulas were so hard
a quick glossary
legacy Excel used arrays far more often than we realized
understanding implicit intersection
breaking implicit intersection
lifting when a scalar is expected but an array is provided
understanding array truncation
using a wrapper function in legacy Excel
preventing implicit intersection with Ctrl+Shift+Enter
from lifting to pairwise lifting
broadcasting makes all arrays the same size
a simple broadcasting example
how do lifting, broadcasting, array truncation, and implicit intersection affect dynamic arrays?
why did Excel add an implicit intersection operator?
answers to the questions at the start of this chapter

10 – XLOOKUP and Dynamic Arrays
XLOOKUP is first new function after dynamic arrays
returning 12 columns of VLOOKUP
returning 12 months with MATCH and several INDEX
returning 12 columns of XLOOKUP without dynamic arrays
returning 12 months with one XLOOKUP
dynamic arrays bug: copying the formula
a two-way lookup with INDEX & MATCH
a two-way lookup with XLOOKUP

11 – Other Functions That Are Now Dynamic Arrays
using TODAY and SEQUENCE for a calendar
NOW and SEQUENCE
generating sequential letters with CHAR, SEQUENCE, and TEXTJOIN
returning the N largest items using LARGE
returning the N smallest items horizontally
transposing with a shorter formula
showing formulas for a range with FORMULATEXT
creating a crosstab report with three formulas
displaying numbers as binary, octal, or hex by using BASE
summing the lengths of many cells
using a formula to convert text to columns
summing all VLOOKUPS
finding the proper case of all names with one formula
replacing a what-if data table with one formula
applying up/flat/down icons by using the SIGN function
using the spilled range operator to point to an array
using an array reference as part of a reference
generating a series of months
forecasting with an array
forecasting 12 months by 5 years
transposing one array to prevent pairwise lifting
forecasting all five years in one formula
wrapping data to columns using INDEX
combining array formulas to simplify cube formulas
using dynamic arrays for dependent validation
there will be hundreds more examples
how will VBA handle dynamic arrays?

12 – Building on Dynamic Arrays
adding totals with FastExcel SpeedTools
excluding certain columns from being totaled
adding totals above and left of the array
unpivoting with a formula
returning slices from an array
stacking multiple arrays or ranges
comparing two lists
a better OR function
how to encourage the calc team to adopt these functions

People also search for Excel Dynamic Arrays 2nd:

excel dynamic array multiple columns
excel dynamic array union
excel dynamic arrays in tables
excel dynamic arrays
dynamic arrays excel 2016

Tags: Bill Jelen, Excel Dynamic