Excel functions

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/168

flashcard set

Earn XP

Description and Tags

Part 1

169 Terms

1
New cards

Input : It takes a range of values or an array (columns or rows)

Output : turns them into a single text representation

=ARRAYTOTEXT(array,format)

2
New cards

=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

3
New cards

=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?

4
New cards

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?

5
New cards

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

6
New cards

Scenario: You want to inspect a two-dimensional array/result

=arraytotext(array,strict)

7
New cards

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

8
New cards

Input : single value of any type
Output : single text value usually in some sort of format of your choosing

=TEXT(value,format)

9
New cards

You want to inspect the formula in a cell i.e you want to see the formula not its output

=formulatext()

10
New cards

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)

11
New cards

I want to format a date, number or % as text

=text(value,format)

12
New cards

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)

13
New cards

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)

14
New cards

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))

15
New cards

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()

16
New cards

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.

17
New cards

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()

18
New cards

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.

19
New cards

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.

20
New cards

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)

21
New cards

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)

22
New cards

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()

23
New cards

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"))))

24
New cards

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 .

25
New cards

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.

26
New cards

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

27
New cards

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)

<p>=MMULT(array_of_scores,array_of_weights)</p>
28
New cards

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.

29
New cards


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

30
New cards
<p>how would you solve the problem in the picture</p>

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 .


31
New cards

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

<p><code>MMULT()</code> doesn't always have to produce a single total. It can calculate multiple related measures for every entity in a dataset</p>
32
New cards

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)

33
New cards

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”

34
New cards

You need to know the row or column number of a cell .


=row(cell)
=column(cell)

35
New cards

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)

36
New cards

You need to check if a cell contains a number

=ISNUMBER(cell)

37
New cards

You need to check if a cell contains a formula

=ISFORMULA(cell)

38
New cards

You need to check if a cell is blank or not

=ISBLANK(cell)

39
New cards

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.

40
New cards

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)

41
New cards

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

42
New cards

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.

43
New cards

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……)

44
New cards

conditions necessary to be able to use CHOOSE()

  1. have a numbered list of options (values, calculations, references)

  2. You want to be able to specify the number and get back the option at that position.

  3. You are confident or can ensure the formula wont receive a number outside of the range.

  4. The options rarely change


45
New cards

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….)

46
New cards

Explain choose() and match() being used together using an example.

47
New cards

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.


48
New cards

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.

49
New cards

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.

50
New cards

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)

51
New cards

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)

52
New cards

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.

53
New cards

Explain using an example SORT() and TAKE() in action together.

54
New cards

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.

55
New cards
term image

=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

56
New cards

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

)

57
New cards

You have a list of conditions. You want true to be returned if an odd number of the conditions are true.

=XOR(logical1, [logical2], ...)

58
New cards

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.

59
New cards

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

60
New cards

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)

61
New cards

You have a Boolean array . You need to count the number of times TRUE appears in the array.

=COUNTIF(array,TRUE)

62
New cards

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)




63
New cards

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”)

64
New cards

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.

65
New cards

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.

66
New cards

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.

67
New cards

=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


68
New cards

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)

69
New cards

You have a report . You need each column to (automatically) get numbered.

=COLUMN()

70
New cards

=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.

71
New cards

You want to be able to count how many fields your report contains assuming that each field is a column.

=columns(report_array)

72
New cards

=columns(A1:E1)

B1 is blank

what will the formula return?

  1. 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.


73
New cards

=INDEX($B$2:$D$2,1,COLUMN(A1))

explain this formula ? what does it return ? when would we use it?


<p></p>
74
New cards

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)

75
New cards

=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.

76
New cards

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.

77
New cards

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)

78
New cards

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

79
New cards

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 match


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.


80
New cards

=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.


81
New cards
<p>=XMATCH(120000,A2:A5,-1)<br><br>=XMATCH(120000,A2:A5,1)<br><br>==XMATCH(120000,A2:A5,0)<br><br>what will the output of each of these? See image</p>

=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

82
New cards

Explain using examples what does search mode control in XMATCH()

Controls search direction and, optionally, the search algorithm.

The four search modes are:

search_mode

Meaning

1

First to last (default)

-1

Last to first

2

Binary search; data must be sorted ascending

-2

Binary search; data must be sorted descending


83
New cards

=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.

84
New cards

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.

85
New cards
<p>see image </p>

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.

86
New cards

XMATCH() vs MATC()?


87
New cards

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.

88
New cards

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], ...)

89
New cards

Think of a PivotTable as a report, and GETPIVOTDATA() as a way to ask that report a structured question.

True or false

True.

90
New cards

=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.

91
New cards
<p>Using the image , answer the following:<br><br>1. How many tickets did Team A resolve?<br>2. How many open tickets does Team B have?<br>3. How many tickets did Team C handle in total?</p>

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”)

92
New cards

=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


93
New cards

=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.


94
New cards

=GETPIVOTDATA("Count of Task ID",$A$3,

"Project",H2,"Status","Completed")


translate this into a business question

  1. Find the number of tickets where project name is H2, and status is completed , assuming the first cell in the pivot table is A3 .


95
New cards

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

96
New cards

Can you use SUMIF() and countif() instead of getpivottable() ? If yes, in what scenarios?

  1. when there is no pivot table

  2. When you want to perform the calculation from the raw data


97
New cards

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

98
New cards

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")

99
New cards
<p>GETPIVOTDATA("Sum of Ticket Count",$A$3,"Team",H2,"Month",H3,"Status",H4)<br><br>translate this into a business question<br><br><br>Assume the source table is named <code>Tickets</code>, with the four columns shown in the image. Which formula calculates the same metric directly from the raw data?</p>

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)

100
New cards

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