1/168
Part 1
Input : It takes a range of values or an array (columns or rows)
Output : turns them into a single text representation
=ARRAYTOTEXT(array,format)
=ARRAYTOTEXT(array,format)
Explain using simple examples
Input : It takes a range of values or an array (columns or rows)
Output : turns them into a single text representation
=ARRAYTOTEXT(array) vs =TEXTJOIN()
arraytotext() - Produces a text representation of the array, including its array structure according to the selected format.
textojoin() - Joins the values using a comma and a space as your chosen delimiter.ARRAYTOTEXT() and TEXTJOIN() are not interchangeable: one represents an array as text , while the other joins values using your chosen separator.
??
does textojoin() work on a list of numbers?
Input : an array or range
Output : comma separated list of text
=textojoin(“separator”,TRUE,array) , here separator can be “,” or “-”
??
what does the true argument specify?
You have an expression that returns an array. You need to quickly inspect the array
=arraytotext(expression)
this outputs the array as a text in one cell for quick inspection
Scenario: You want to inspect a two-dimensional array/result
=arraytotext(array,strict)
You want to join text and values without automatically inserting delimiters .
=concat() - modern function
=concatenate() - legacy option
=text1&text2&text3 - only works if the individuals values are text
Input : single value of any type
Output : single text value usually in some sort of format of your choosing
=TEXT(value,format)
You want to inspect the formula in a cell i.e you want to see the formula not its output
=formulatext()
You have an array and want to a single cell containing the array as a comma separated list . If the array contains blank/empty values/cells you want to ignore them.
=TEXTJOIN(", ",TRUE,array)
I want to format a date, number or % as text
=text(value,format)
You have a range of data i.e a table with multiple columns and rows. You want to process one column at a time and return the result for each column . In other words , You want to perform the same operation on each column , one at a time. For e.g calculating the sum of each column
option 1 : using regular sum()
option 2 : bycol(range,repeated_operation) where repeated_operation = lambda(col, action) for e.g action could be sum(col)
You have a range of data i.e a table with multiple columns and rows. You want to process one row at a time and return the result for each row . In other words , You want to perform the same operation on each row , one at a time. For e.g calculating the sum of each row
option 1 : using regular sum()
option 2 : byrow(range,repeated_operation) where repeated_operation = lambda(row, action) for e.g action could be sum(row)
Your worksheet contains ticket counts for 20 support agents. Each row is an agent, and each column is a month.
You want to calculate the total number of tickets handled by the entire team in each month, using one formula.
BYCOL() with sum()
=bycol(range,lambda(col,sum(col))
You have an array. You want to perform an operation or transformation to each element in the array and return the new array.
In other words, You want to perform a lambda() to each element in the array and return the new array.
=map()
You want to be able to create a new array by supplying row and column indexes.
In other words, you want to be able to create an array by specifying like logic for determining the value of each element in the array.
=makearray(rows,columns,repeated_logic)
=makearray(rows,columns,lambda(row,col,calculation)
repeated_logic contains the logic for calculating the value of each cell in the array.
You have an array aka table. You want to swap the orientation of the data i.e turn the rows into columns and vice versa
=transpose()
You have an array . You need to perform the same operation on every element within the array and return the transformed/new array
=map(array,repeated_operation) where repeated_operation = lambda(e,some operation involving e)
Here e is simply a variable representing the current element in the array being processed.
What does bycol() / byrow() have in common with map()
both use lambda because both require repeatedly carrying out an operation on a collection of entities. In map, the entities are elements of an array. In bycol/byrow the entities are a column/row in a collection of columns/rows.
You need to create an array from scratch . You want to specify the size of the array and the logic for calculating the value of each element/cell of the array .
In other words, you need to build a grid using a formula.
=MAKEARRAY(rows,cols,logic_for_determining_each_cell) where logic_for_determining_each_cell is lambda(current_row,current_col, calculation)
You need to create an array from scratch such that the formula for the value of each element in the array involves its row and column numbers
=MAKEARRAY(rows,cols,logic_for_determining_each_cell) where logic_for_determining_each_cell is lambda(current_row,current_col, calculation)
Does =makearray() require the use of lambda() ? Explain
Yes. Makearray() involves creating a array from scratch by repeatedly performing the same operation for each element in the array. The only thing that changes is the row and column numbers of each element. Thus lambda is required because anytime we need to repeat the same operation for each element, we use lambda()
you want a 5-by-5 grid where:
Cells on the diagonal show X.
Cells above the diagonal show Above.
Cells below the diagonal show Below.
=MAKEARRAY(5,5,LAMBDA(r,c,IF(r=c,"X",IF(r<c,"Above","Below"))))
You have two arrays. You need to perform matrix multiplication using the two arrays
=MMULT(array1,array2) where array1 is the array of quantity/scores and array2 is the array of unit value or weights
In real world applications, the order of the columns in array1 must match the order of the rows in array2 .
define matrix multiplication
Matrix multiplication involves two arrays such that the number of columns of the first array must match the number of rows in the second array
Process: Take a row from the first array, multiply its values by the corresponding values in a column from the second array, and add the results. Repeat for every row in the first array.
can we perform matrix multiplication involving a 2×3 array and 3×2 array? If yes, why and what is the shape of the result?
Yes because it fits the rule - the number of columns of the array1 is equal to the number of rows of array2 . Shape of the result is 2×2.
general rule - (mxn) (nxp) = mxp
You have an array containing scores for a number of categories . You have an array containing the weights/ importance/ priority of each category . The categories appear in the same order in both . How can you calculate the overall performance .
=MMULT(array_of_scores,array_of_weights)

Scenario : you need to combine many numbers using a consistent set of weights or relationships
that is a clue you need?
=MMULT(array_of_scores,array_of_weights)
Each row in array_of_scores represents activity . Multiplying each element in the row with a shared set of rates or weights - performance of that row.
Intuition: The first array contains activity; the second contains the value assigned to each activity. You need to calculate total revenue . What do you use
=MMULT(array1,array2)
where array1→array where each row represents activity of a certain entity across a set of categories e.g salesperson sales of a set of products.
where array2→ array representing rates/weights of each category for e.g product

how would you solve the problem in the picture
There are two arrays here. Array1 is specifies units sold across two products by each store(entity) . Array two defines the rates (cost and selling price) for each product .
Calculating the total cost and revenue for each store is essentially a MMULT() problem involving multiple outputs per row .
MMULT() does not always return one output per row. Prove this using an example
MMULT() doesn't always have to produce a single total. It can calculate multiple related measures for every entity in a dataset

You need to a row of quantities or scores to be multiplied by a column of values or weights. How to do you do it?
=MMULT(array_of_quantities,array_of_weights)
You need to know about the different properties of a cell - such as its address, number format, width , workbook and worksheet location etc .
What formula do you use?
=cell(info_type,reference)
where info_type can be : “address” or “type”
You need to know the row or column number of a cell .
=row(cell)
=column(cell)
A cell contains a number . You want to know how it is formatted - for e.g to 2 decimal places vs to %
=cell(“format”,cell_reference)
You need to check if a cell contains a number
=ISNUMBER(cell)
You need to check if a cell contains a formula
=ISFORMULA(cell)
You need to check if a cell is blank or not
=ISBLANK(cell)
CELL() should be avoided if possible in large workbooks. Explain why?
CELL() can be recalculated more often than you might expect, and using many such formulas in a large workbook can affect performance. Use it where its metadata is useful rather than as a replacement for ordinary cell references.
You have an array of numbers with an expected format. You need to check if they actually have the expected format. How?
=IF(cell(“format”,cell)=expected_format,"TRUE”,FALSE)
You want to create a numbered menu of options . Each option is defined by its position in the sequence. You give excel a number, and it will return you the option positioned at that number.
=CHOOSE(index_num, menu_of_options)
where menu = option1,option2,option3…..
e.g =CHOOSE(2,"Daily report","Weekly report","Monthly report") —> returns weekly report
CHOOSE() and IF() are alternatives in many situations. Explain with example.
=IF(A2=1,"Poor",
IF(A2=2,"Fair",
IF(A2=3,"Good",
IF(A2=4,"Excellent","Invalid"))))
vs
=CHOOSE(A2,"Poor","Fair","Good","Excellent")
You don't need multiple nested IF() statements when the categories are already numbered.
You want build a report such that the user can click on the metric for e.g monthly/weekly and the report will update dynamically
=CHOOSE(input_,metric1,metric2,metric3……)
conditions necessary to be able to use CHOOSE()
have a numbered list of options (values, calculations, references)
You want to be able to specify the number and get back the option at that position.
You are confident or can ensure the formula wont receive a number outside of the range.
The options rarely change
You have a list of key:value pairs where the keys are text. You want to be able to specify a key and if a match is found, you want the associated value to be returned.
=SWITCH(input,key1,value1,key2,value2….)
Explain choose() and match() being used together using an example.
You have a table. You want to select/extract certain columns in a specific order and only return them in that order
You have a table. You want to select/extract certain rows in a specific order and only return them in that order.
CHOOSECOLS(array,col_num1,[col_num2....]) lets you select which columns you want to keep.
CHOOSEROWS(array,row_num1,[row_num2.....]) lets you select which rows you want to keep.
what does the formula below return:
=CHOOSEROWS(A1:E6,-1)
from the table A1:E6, returns the last row .
-2 would mean second last row.
In CHOOSECOLs() and CHOOSEROWS() you cannot specify the order in which the columns are returned. Evaluate this statement.
False.
=CHOOSECOLS(A1:E6,5,1,2)
this returns the 5th , 1st and 2nd column within the specified array.
You have transactional data stored in chronological order. You want to see the last 5 rows. How?
=CHOOSEROWS(A2:F1000,-5,-4,-3,-2,-1)
You have a table/array. You need the value of a specific cell and you know the row number of column number of the cell. How can you do it?
=index(array,r,c)
CHOOSEROWS() and CHOOSECOLS() vs TAKE()
CHOOSEROWS() is a more flexible . TAKE() only allows you to extract a contiguous block of cells either from the beginning or end.
Explain using an example SORT() and TAKE() in action together.
The situation: You receive a dataset with 20 columns, but your analysis only needs customer ID, order date, product ID, quantity, and revenue.
Suppose your source data occupies A1:T5000, and those fields are in columns 1, 3, 6, 9, and 15.
=CHOOSECOLS(A1:T5000,1,3,6,9,15)
You create a smaller analytical dataset without manually copying columns to a new worksheet.

=CHOOSECOLS(
FILTER(A2:E1000,(C2:C1000="High")*(E2:E1000<>"Closed"),"No matches"),
1,2,3
)
Mental model: Use FILTER() to identify the right records, then CHOOSECOLS() to shape the output
Imagine a support manager asks:
Show me the five most recent high-priority tickets, but only display Ticket ID, Agent, and Status. But I only want to see the columns 1,2,5 of the table.
Assume the data is in A1:E1001, with headers in row 1.
=CHOOSECOLS(
TAKE(
SORT(
FILTER(A2:E1001,C2:C1001="High","No matches"),
1,-1
),
-5
),
1,2,5
)
You have a list of conditions. You want true to be returned if an odd number of the conditions are true.
=XOR(logical1, [logical2], ...)
OR() Vs AND() Vs XOR()
AND() = Both must be TRUE.
OR() = At least one must be TRUE.
XOR() = An odd number must be TRUE. With two conditions, exactly one must be TRUE.
Imagine A2 contains 80 and B2 contains 60. You want to know whether exactly one score exceeds 70.
=XOR(A2>70, B2>70)
=XOR(true, false)
=TRUE
You have two conditions. You don't want both to be true. You want only one of them to be true. If only one of them is true , you want the formula to return true.
=XOR(c1,c2)
You have a Boolean array . You need to count the number of times TRUE appears in the array.
=COUNTIF(array,TRUE)
You need two flags that are mutually exclusive . You need to validate that th current values of both flags are mutually exclusive. For e.g suppose you have resources and the “is morning shift" flag and “is night shift” flag.
A resource cannot be assigned to both shifts.
=if( XOR(c1,c2),”valid”,invalid)
Scenario
A project should be escalated if either:
Its budget variance exceeds 10% (B2)
Its schedule delay exceeds 7 days (C2)
You want to identify projects that trigger exactly one of these two escalation conditions.
=if(XOR(B2>10%,C2 >7), “meets exactly one condition”, “does not meet exactly one condition”)
Suppose a BA compares a customer record in two systems:
B2: CRM says the customer is active.
C2: Billing system says the customer is active.
How can we identify if there is a discrepancy between the two systems?
=XOR(B2=”Active”, C2=”Active”)
If you get back TRUE that means there is a discrepancy . You only get back TRUE if one of them is true and the other one is false.
want to know if a product that was available yesterday is unavailable today. There are two flags to monitor each.
B2: Available yesterday?
C2: Available today?
In general: you want to detect changes in a binary indicator/KPI
=XOR(B2,C2)
XOR is useful whenever you want to know whether a binary state has changed between two observations.
You want to know the column number of a particular cell
=column([cell])
if no cell is provided, the column number of the cell where the formula is applied is returned.COLUMN() returns the numeric position of a column in the worksheet.
=COLUMN(B2:E10)
=COLUMN(B2:E2)
what do each of the formula return ?
The first onee returns 2, because the first column of that reference is column B. Formula one got a table array as input.
The second formula got a row array as input. So it will return the column number of every element in the range. The column numbers will spill out
You have a range/array . You need to know the width of the range/array aka the number of columns in the range/array.
=COLUMNS(array)
You have a report . You need each column to (automatically) get numbered.
=COLUMN()
=COLUMN(A1)-COLUMN($C$1)+1
what does this return and why?
When would we use this?
=1-3+1
=-2
This means the cell is two columns to the left of the column that is being considered the starting column (C) .
This formula would be used if we want to start numbering from column C.
You want to be able to count how many fields your report contains assuming that each field is a column.
=columns(report_array)
=columns(A1:E1)
B1 is blank
what will the formula return?
Columns does not care if the cell is blank or not. It still counts blank values which might be a problem and therefore should be remembered.
=INDEX($B$2:$D$2,1,COLUMN(A1))
explain this formula ? what does it return ? when would we use it?

You have a range of values that can grow i.e the number of values in the range can go up or down.
How do you calculate the average value of such a volatile range?
=SUM(range)/COLUMNS(range) such as
=SUM(B2:M2)/COLUMNS(B2:M2)
=SUM(B2:M2)/COLUMNS(B2:M2)
vs
=SUM(B2:M2)/COUNT(B2:M2)
What is the difference between the two?
Both can be used to calculate average . However the difference is how they treat blank values. Columns() still counts blanks while count() does not .
Use whichever one makes more sense for the context.
You have an array/range/table. You want to know the column number of the last column in hte array/range/table.
Assume the array is B2:G100.
=COLUMN(B2)+COLUMNS(B2:G100)-1
This can be useful when a formula needs to calculate the position of the last column relative to a known starting point.
You have list and a search element located in the list. You specify the search and you want to be returned the position of the match within the specified list /range , not the matching value itself.
=xmatch(search_value, list)
xmatch() Vs index()
Xmatch() is used to answer the question - what is the position of this value
Index() is used to answer the question - what value is stored in this position
Explain with examples what different arguments of mode parameter does to the output
0 — Exact match (default)
-1 — Exact or next smaller
1 — Exact or next larger
2 — Wildcard match
Match mode controls what counts as a match0 — Exact: Find a specific employee ID, ticket number, SKU or outlet code. This should be your default mental setting.
-1 — Exact or next smaller: Find the appropriate lower threshold when mapping a value to a tier.
1 — Exact or next larger: Find the next qualifying threshold above a value.
2 — Wildcard: Search using a partial text pattern when you do not know the complete name or identifier.
=XMATCH(75,A2:A5,-1)
=XMATCH(75,A2:A5,0)
=XMATCH(75,A2:A5,1)
=XMATCH(75,A2:A5,2)
Explain the difference between these 4 variations .
0 — Exact: Find a specific employee ID, ticket number, SKU or outlet code. This should be your default mental setting.
-1 — Exact or next smaller: Find the appropriate lower threshold when mapping a value to a tier.
1 — Exact or next larger: Find the next qualifying threshold above a value.
2 — Wildcard: Search using a partial text pattern when you do not know the complete name or identifier.
The distinction between -1 and 1 is important. In Excel's XMATCH, -1 means the next smaller item, while 1 means the next larger item.

=XMATCH(120000,A2:A5,-1)
=XMATCH(120000,A2:A5,1)
==XMATCH(120000,A2:A5,0)
what will the output of each of these? See image
Explain using examples what does search mode control in XMATCH()
Controls search direction and, optionally, the search algorithm.
The four search modes are:
| Meaning |
|---|---|
| First to last (default) |
| Last to first |
| Binary search; data must be sorted ascending |
| Binary search; data must be sorted descending |
=XMATCH("T-104",B2:B6,0,2) is likely to be wrong if a condition is not true. What is this condition?
If the search mode argument is either 2 or -2 , then XMATCH() performs Binary search. Binary search requires the dataset to be sorted in ascending and descending order respectively.
For most business work, use 1 or -1. Binary search can be useful for large, correctly sorted ranges, but an incorrectly sorted range can produce an incorrect result without an obvious error.
Explain why index() and xmatch() are often combined together
Seperation of concerns resulting in more flexible logic. Finding logic and retrieval logic are now decoupled.

see image
=INDEX(B2:D4,
XMATCH("Nadia",A2:A4),
XMATCH("February",B1:D1))
This is a two-way lookup problem. Because you have a table and need the value of a specific cell within the table. Two XMATCH() allows us to find the value of r and c. Finally Index() allows us to perform value retrieval.
XMATCH() vs MATC()?
XMATCH() vs XLOOKUP() . When to use which?
Both are lookup functions but they return different things and hence answer different questions.
You need the matching value directly: Use XLOOKUP() instead.
What is GETPIVOTDATA() ? Why does it exist?
You have a Pivot Table . You want to extract information from it. You want to retrieve the answers to questions the pivot table is already asking. You don’t want to have to view it interactively or reference cells from it directly because the layout of the pivot table can change.
This is when GETPIVOTDATA() is useful. GETPIVOTDATA() allows us to retrieve a value from a PivotTable by describing what the value represents instead of its position.
=GETPIVOTDATA(data_field/measure , pivot_table, [field1, value1], ...)
Think of a PivotTable as a report, and GETPIVOTDATA() as a way to ask that report a structured question.
True or false
True.
=GETPIVOTDATA("Resolved Tickets",$A$3,"Team","Team A")
Translate this into a business question.
What is the total number of resolved tickets for Team A?
Illustrative example: this assumes the PivotTable has a value field named Resolved Tickets, a row field named Team, and its top-left cell is A3.

Using the image , answer the following:
1. How many tickets did Team A resolve?
2. How many open tickets does Team B have?
3. How many tickets did Team C handle in total?
=getpivotdata(measure/data_field,start_of_pivot_table,[key1,value1]….)
conditions are defined by using key:value pairs where each key is a field/dimension .
1. =getpivotdata(“Tickets”,$A$3,”Team”,”Team A”,”Status”,”Resolved”)
2. =getpivotdata(“Tickets”,$A$3,”Team”,”Team B”,”Status”,”Open”)
3. =getpivotdata(“Tickets”,$A$3,”Team”,”Team C”)
=getpivotdata() may use key:value or field:value pairs as arguments. Explain why?
Key intuition: each field/item pair narrows the question. Add Team to select a team, add Status to select a status, and so on.
for e.g getpivotdata(“Tickets”,$A$3,”Team”,”Team A”,”Status”,”Resolved”) means you want number of tickets where
a) the team was team A
b) the status was resolved
=GETPIVOTDATA("Tickets",$A$3,"Team",H2,"Status","Resolved")
benefit of writing H2 instead of hardcoding the value?
You change H2 from Team A to Team B, and the requested team changes automatically.
You can build a dashboard without manually linking each result to a different PivotTable cell.
You can use dropdowns to let managers choose a team or reporting period.
=GETPIVOTDATA("Count of Task ID",$A$3,
"Project",H2,"Status","Completed")
translate this into a business question
Find the number of tickets where project name is H2, and status is completed , assuming the first cell in the pivot table is A3 .
Scenario : Retrieve a specific summarized value from a PivotTable that the table is already providing
GETPIVOTDATA(measure,start,key1,value1,key2,value2…..)
start is the cell from which the pivot table starts i.e the left-top edge of the table
Can you use SUMIF() and countif() instead of getpivottable() ? If yes, in what scenarios?
when there is no pivot table
When you want to perform the calculation from the raw data
GETPIVOTDATA() is designed to be resilient to changes in cell positions and structural changes. If you rename a field result, the formula will not break .
true or false
false.GETPIVOTDATA() is designed to be resilient to changes in cell positions, but it is not immune to structural changes. If you rename a field, remove it from the PivotTable, or filter out the required result, the formula may stop working
Suppose your PivotTable contains ticket counts by Team and Status, and the PivotTable starts at A3.
You want the number of open tickets for Team B. Cell H2 contains Team B
=GETPIVOTDATA("Tickets",$A$3,"Team",H2,"Status","Open")

GETPIVOTDATA("Sum of Ticket Count",$A$3,"Team",H2,"Month",H3,"Status",H4)
translate this into a business question
Assume the source table is named Tickets, with the four columns shown in the image. Which formula calculates the same metric directly from the raw data?
=SUMIFS(Tickets[Ticket Count],Tickets[Team],H2,Tickets[Month],H3,Tickets[Status],H4)
You have a list of elements. You want to take the list and transform it into a table . You want to be able to specify the shape of the table.
How?
Option 1: WRAPROWS() fills a table across each row, then moves down to the next row. Essentially use this approach if you want the table to be constructed one row at a time.
Option 2: WRAPCOLS() fills a table down each column, then moves right to the next column. Essentially use this option if you want the table to constructed one column at a time