Mastering SEQUENCE: Excel's most amazing function with more than 200 examples

Book title
Inner title
Copyright
Dedicated
About the Author
About the Reviewer
Acknowledgement
Preface
Coloured Images
Piracy
Table of Contents
Chapter 1: A Short Introduction to Dynamic Array Functions in 
Excel 365
	Introduction
	Structure
	Objectives
	Introducing Dynamic Array functions
	Examples of the new DAF
		UNIQUE function
		FILTER function
		SORT function
		SORTBY function
		RANDARRAY function
		SEQUENCE function
		TEXTSPLIT function
		TOCOL function
		TOROW function
		VSTACK function
		VSTACK versus TOCOL
		HSTACK function
		EXPAND function
		ARRAYTOTEXT function
		TEXTBEFORE function
		TEXTAFTER function
		CHOOSECOLS function
		CHOOSEROWS function
		WRAPROWS function
		WRAPCOLS function
		XLOOKUP function
		XMATCH function
		TAKE function
		DROP function
		VALUETOTEXT function
	Conclusion
	Points to Remember
Chapter 2: SEQUENCE in Text Operations
	Introduction
	Structure
	Objectives
	Examples of SEQUENCE with text
		Finding the names of the 10 highest-paid employees
		How many words are there in the cell (version 1)
		How many words are there in the cell (version 2)
		How many times does a string appear in the cell
			Method 1 of 5
			Method 2 of 5
			Method 3 of 5
			Method 4 of 5
			Method 5 of 5
		Extract all characters - Horizontally
		Extract all characters – Vertically
		All uppercase English in one column
		All uppercase English in one cell
		Duplicate a sequence of characters
		Duplicate a cell by a duplication factor
			Method 1
			Method 2
		Creating English uppercase letters without knowing how many letters there are
		Transpose without TRANSPOSE
		Extract only first three letter of weekday names
		Extract only digits from a string
			Method 1 of 3
			Method 2 of 3
			Method 3 of 3
		Extract only unique Alphabetic characters from a string
		Split numbers and text
		Remove unwanted characters from string (2 named ranges as parameters)
		Remove unwanted characters from string (Formula)
		How many times does a string appear in a range
		Is it a Palindrome?
		Add vendor to list (the table)
		Add vendor to list (the formula)
		Remove all digits from the string
		Move first name from end of cell to the beginning
		Reverse String
			Method 1
			Method 2
		Sort Text in alphabetical order
		How many words are there in the cell without the separator (SEQUENCE)
		How many words are there in the cell without the separator (TEXTSPLIT)
		Off with their heads
		Extract only digits and add a separator
		How many lower-case letters are there in the cell
			Method 1
			Method 2
		All Greek letters in one formula
		Find last word in cell
			Method 1 of 3
			Method 2 of 3
			Method 3 of 3
		Number of characters in cell (without the separator)
		Number of non-empty cells in a column
		Strip leading and trailing digits
			Method 1 of 2
			Method 2 of 2
		Increasing Text from end to start
		Increasing Text from start to end
		Hebrew Gematria (Formula)
		Hebrew Gematria (Gtable – Translation table)
		Extract only Country Names
		How many occurrences of a String starting from a certain position
		Remove Diacritics from Hebrew words
		Is it a Palindrome (Arabic)
		Convert Hebrew letters into English letters
		Fetch description of Nth item of a non-sorted Key
		Extract letters only from a chosen language (Formula)
		Extract letters only from a chosen language (Validation list)
		Gematria in English
			Method 1
			Method 2
		How many Words are there in a Range?
		Extract only non-digits from String
		Find Unicode value for any character in the string, no matter which language
	Conclusion
	Points to remember
Chapter 3: Using SEQUENCE with Numbers
	Introduction
	Structure
	Objectives
	Examples of SEQUENCE with numbers
		Five methods to generate 12 positive integers
			Method 1
			Method 2
			Method 3
			Method 4
			Method 5
		Five methods to generate 12 negative integers
			Method 1
			Method 2
			Method 3
			Method 4
			Nethod 5
		Descending SEQUENCE – Two methods
			Method 1
			Method 2
		Duplicate cell vertically
		Duplicate numbers
		Creating a vertical SEQUENCE of numbers – Two methods
		Find missing numbers in a list
		Reverse a Number
		Reverse a horizontal ascending array
		Reverse a horizontal descending array
		SEQUENCE of odd and even numbers
		Sum all digits in a cell which has only digits
		Sum all digits in a cell which has digits and tex
		Sum every Nth row
		Sum the largest N numbers
		Sum the smallest N numbers
		Two tricks with SEQUENCE (ROW())
			Create a SEQUENCE of n Rows starting from Row(n)
			SUM a virtual array created by SEQUENCE (ROW())
		SUM SEQUENCE (virtual array)
		Alternate 1s and 0s
		Dynamic SEQUENCE
		Two methods to extract a number from the string's end
			Method 1
			Method 2
		Two methods to extract a number from the string's start
			Method 1
			Method 2
		Find N largest numbers (Ascending)
		Find N largest numbers (Descending)
		How many columns in a sheet
		How many digits
		Reverse numbers horizontally by a parameter
		Reverse order of a SEQUENCE of numbers
		Subject with the highest score
		SEQUENCE based on number of unique values
		Dynamic frequency based on dynamic bins
		SEQUENCE column
			SEQUENCE and COLUMNS
		Building a chessboard in three steps
			Chessboard - Step 1
			Chessboard - Step 2
			Chessboard - Step 3
		Creating N-digit number with the same digit repeated N times
	Conclusion
	Points to remember
Chapter 4: SEQUENCE in Arrays
	Introduction
	Structure
	Objectives
	Examples of SEQUENCE with arrays
		Creating an array of identical numbers - Two methods
		Creating an array of ascending numbers – three methods
		From one cell to a vertical array
		How many active months
		Build a dynamic array - horizontal or vertical
		Flip columns horizontally
			Method 1
			Method 2
		Flip vertical array (with and without SEQUENCE)
		Flip part of vertical array using a parameter
		Create a two-dimensional array using four parameters
		Flexible LARGE
		MMULT with static ranges and with dynamic Arrays
		Four useful tricks with VLOOKUP
			VLOOKUP - Fetch all columns of an item searched
			VLOOKUP - Fetch all data per lookup key in reverse order
			VLOOKUP - Fetch last two columns for a search key
			VLOOKUP - Fetch the first and third data items per lookup key
		From vertical to horizontal – Two Methods
			Method 1
			Method 2
		Four two-dimensional arrays generated by two parameters
		Select columns by parameters
		Transpose a vertical array without knowing its size beforehand
		Fetching multiple results for a search value
		Vertical to horizontal without TRANSPOSE
	Conclusion
	Points to remember
Chapter 5: SEQUENCE in Date and 
Time Operations
	Introduction
	Structure
	Objectives
	Examples of SEQUENCE with date and time
		12 months with each month's first day
		The year’s months with the last day of each month - three methods
			Method 1
			Method 2
			Method 3
		N consecutive dates starting from today (two methods)
			Method 1
			Method 2
		Display month names without a specific date
		Two methods to display the weekday names
		Adding minutes to time
		Adding seconds to time
		Sequence of days in a given month
		A SEQUENCE of dates (between start and end dates)
		Extract only time –four traditional methods
		Extract only time – a new method
		Presence in class by month
		How many Mondays are there in a given month
		How many Saturdays between two dates?
			Method 1
			Method 2
		Display only dates of Wednesdays in 2020
		How many Wednesdays are there in 2020?
		Sequence of the month’s last date for each month (two methods)
			Method 1
			Method 2
		A horizontal SEQUENCE of descending dates - First of each month
		A substitute for NETWORKDAYS.INTL (for a certain month)
			The MonCal Named Range
		A substitute for the NETWORKDAYS.INTL (any period)
		A substitute for NETWORKDAYS.INTL - with/without weekends
			The Definition of MonCal
		How many working days are there in each month of a given period?
		How many eligibility days?
		The doctor's schedule (two versions)
			The doctor's schedule (version 1)
			The doctor's schedule (version 2)
		Monthly calendar – classic versus non-classic
			Monthly calendar - classic
			Monthly calendar – non-classic
		Monthly calendar in 20 languages
			Monthly calendar in 20 languages – list of languages and formats
			Monthly calendar in 20 languages – list of month numbers
		Two methods for creating a list of the month’s days
			Monthly calendar – a bad attitude
			Monthly calendar – a good attitude
		Yearly calendar – good versus bad
		Dynamic yearly calendar – in one formula
		Dynamic yearly calendar – Conditional Formatting
		Dynamic yearly calendar - by month
		Dynamic yearly calendar - by week
		Yearly Horizontal calendar with highlighted weekday (two examples)
		Yearly horizontal calendar
			Example 1 (weekday chosen: Sunday)
			Example 2 (weekday chosen: Saturday)
			Yearly horizontal calendar - Conditional Formatting - calendar
			Yearly horizontal calendar - Conditional Formatting - weekday names
		Yearly vertical calendar with highlighted weekday (2 examples)
			Yearly vertical calendar – example 1
			Yearly vertical calendar – example 2
			Yearly vertical calendar – Conditional Formatting - calendar
			Yearly vertical calendar – Conditional Formatting – weekday names
		Yearly calendar: one formula with Conditional Formatting
		Conditional Formatting – each weekday is formatted differently
	Conclusion
	Points to remember
Chapter 6: Financial Operations with SEQUENCE
	Introduction
	Structure
	Objectives
	Examples of SEQUENCE with financial functions
		Loan return by payments per period
		PMT - Periodic payment of a loan – traditional method
		Periodic payment of a loan – a more flexible method
		The Depreciation function in Excel – DB
		Equally divide a sum of money over a period of time
		One formula - How varying loan amounts impact the loan’s installments
		NPV – No need for a data table
		PDURATION - Multiple results
		The RATE function – multiple results
		RRI - calculate the average annual interest rate of an investment
		SEQUENCE and SUM
	Conclusion
	Points to remember
Chapter 7: SEQUENCE -The Ancilla of 
Math
	Introduction
	Structure
	Objectives
	Examples of SEQUENCE in math operations
		A number to the power of
		Two methods to create a sequence of square roots
		Two methods to generate a sequence of fractions
		Creating a sequence of alternate 1’s and 0’s
		Dynamic quadratic equation
		SUM - a virtual Array
		How many candles
		Raising the number 2 to the power of 10 using bit operation
		Simplest OR
		Dynamic Sine with two Spin buttons
		Exponential Growth example
		Dynamic multiplication table
		BIN2DEC – MMULT and SEQUENCE
		BIN2DEC - SUM (or SUMPRODUCT) with SEQUENCE
		Filling the missing values in a geometric series
		Trigonometry with SEQUENCE
		An array of duplicate numbers generated by bit operations
		Using MMULT and SEQUENCE to track wins, losses, and ties in each quarter
		Digital root
		First n odd numbers squared (A simple solution)
		First N odd numbers squared (A complex solution)
		Find first divisor of a number (divisor found)
		Find first divisor of a number (divisor not found)
	Conclusion
	Points to remember
Chapter 8: SEQUENCE and Other Animals
	Introduction
	Structure
	Objectives
	Examples of SEQUENCE with other animals
		Better than nested IF
			Traditional solution – nested IF
			XLOOKUP and SEQUENCE instead of nested IF (example 1)
			XLOOKUP and SEQUENCE instead of nested IF (example 2)
			XLOOKUP and SEQUENCE instead of nested IF (example 3)
		Fetch the first and last digits from a string
		Data validation – only Hebrew letters
		Data validation – only uppercase English letters
		Splitting cell by chunk size and separator (two examples)
		Remove all digits from string
		Remove names and split numbers to separate cells
		Removing “A”, “B” and “C” from string – two methods
			Method 1
			Method 2
		INDEX-SQRT instead of FILTER
		Remove all uppercase or lowercase letters from string
		Verifying the validity of a check digit with the Luhn algorithm
	Conclusion
	Points to remember
Index
Back title