The definitive guide to data sequencing and manipulation in Excel KEY FEATURES ● Unleash the power of SEQUENCE to simplify complex array calculations and automate repetitive tasks. ● Discover techniques to efficiently perform calculations, text manipulation, financial and numerical analysis using SEQUENCE. ● Learn how to integrate SEQUENCE with other Excel functions and tools. DESCRIPTION The SEQUENCE function in Excel 365 allows you to generate sequences of numbers or values based on specific criteria. By utilizing this function, you can effectively manage a wide range of numerical and data manipulation tasks. If you're looking to leverage its dynamic capabilities to enhance your productivity, this book is an ideal resource for you. This book provides the most comprehensive coverage of the SEQUENCE function, which is widely considered the most versatile function in Excel 365. It serves as a detailed introduction to the new Dynamic Array Functions, offering examples for a better understanding of this new revolutionary concept. Additionally, the book delves into the extensive applications of SEQUENCE in various areas, including text functions, number manipulation, arrays, date and time operations, financial calculations, math, and complex formulae involving SEQUENCE, with a special focus on the super-function: LAMBDA. With over 200 examples, this book allows you to actively engage and explore the multifaceted dynamism of the SEQUENCE function. By the end of the book, you will be able to confidently apply the SEQUENCE function in your own Excel workflows, enhancing your productivity and efficiency. WHAT YOU WILL LEARN ● Explore advanced techniques to enhance text-based analysis using SEQUENCE. ● Understand how SEQUENCE can generate dynamic arrays with custom patterns, sizes, and dimensions. ● Learn how to use the LAMBDA function to solve complex calculations. ● Gain insights into using SEQUENCE to streamline financial modeling and forecasting. ● Get tips and tricks to optimize date and time-related calculations using SEQUENCE. WHO THIS BOOK IS FOR This book is for everyone who is eager to explore innovative approaches in Excel, expand their knowledge, and improve their problem-solving skills. It serves as a valuable resource, offering a wide range of techniques that you can apply to enhance their Excel proficiency. TABLE OF CONTENTS 1. A Short Introduction to Dynamic Array Functions in Excel 365 2. SEQUENCE in Text Operations 3. Using SEQUENCE with Numbers 4. SEQUENCE in Arrays 5. SEQUENCE in Date and Time Operations 6. Financial Operations with SEQUENCE 7. SEQUENCE - The Ancilla of Math 8. SEQUENCE and Other Animals
Author(s): Meni Porat
Year: 2023
Language: English
Pages: 248
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
