Email: mikregetuinfo@gmail.

com

Password:

Mihrete1234!

Courses
 Functions & Formulas -
Array Formulas
Course description
This course teaches how to visualize data effectively using Excel charts.
Learn to choose the right chart types, customize charts for clarity and impact,
and interpret the visual data to make informed decisions.

Functions & Formulas - Array Formulas

Part 1 - Array Formulas1:43 min
"Using array formulas can substantially reduce the complexity of calculations
when dealing with large arrays of data. This session looks at how to set up an
array formula. After completing this session, you will be able to use an array
formula and evaluate parts of it in the formula bar. Learning objectives; •
Perform multi-step calculations without an array formula • Complete the same
calculations using an array formula • Evaluate a part of the array formula in
the formula bar"

How do you tell Excel that you are using an array formula?

a. Press Enter
b. Press Control, Shift, and Enter
c. Press Alt, Shift, and Enter
 What is an advantage of using an array formula in Excel?

a. It allows you to format cells with multiple colors.

b. It enables you to perform calculations across multiple cells without
needing intermediate columns.
c. It automatically updates all formulas in a worksheet.

What does pressing F9 on a part of an array formula in Excel do?

a. It deletes the selected part of the formula.

b. It evaluates the selected part and displays the calculated values.
c. It converts the selected part into a regular formula.

Part 2 - Count Errors1:50 min

"When working with large datasets, it might be important to count the total
number of errors present in the dataset or the number of errors of a particular
type. This session covers how to count the number of errors in a worksheet.
After completing this session, you will be able to count the number of errors
using an array formula. Learning objectives; • Use the ISERROR function to
check if there is an error in a particular cell • Count the total number of errors
by setting up an array formula combining the ISERROR, IF and COUNT
functions • Count the number of errors of a particular type using the COUNTIF
function"

What function can we use to check if there is an error in a particular cell in a

dataset?

a. is-error
b. is-error-check
c. error-check

How do you modify the ISERROR function to count the number of errors across a
dataset?

a. Use IF(ISERROR(cell), 1) and then use the SUM function.

b. Use IFERROR(ISERROR(cell), 1) and then use the COUNT function.
c. Use IF(ISERROR(cell), 1, 0) and then sum the results.

If you want to count specific errors, such as #REF!, which function would you use
in Excel?

a. COUNTERROR
b. COUNTIF
c. COUNTIFS

Part 3 - Count Unique Values 3:51 min

"An important skill is to be able to quickly identify and count unique values in a
dataset, especially if working with large amounts of data. This session looks at
how to count unique values in an array of data. After completing this session,
you will be able to count unique values and values that occur exactly once.
Learning objectives; • Count unique values with the UNIQUE function in Excel
365 • Count values that occur exactly once by specifying optional parameters
of the UNIQUE function in Excel 365 • Achieve the same outcome in older
versions of Excel by setting up array functions"

Which function is used to count unique values in Excel 365?

a. UNIQUE
b. COUNT
c. SUM

In older versions of Excel without the UNIQUE function, how can you count
unique values using array formulas?

a. Use COUNTIF with specific criteria.

b. Use SUMIF with the criteria set to count unique values.
c. Use COUNTIF as an array formula, invert the results, and then sum
them.

To count values that occur exactly once in Excel 365 using the UNIQUE function,
what additional argument should you include?

a. Set the second argument to TRUE.

b. Set the second argument to FALSE.
c. Set the third argument to TRUE.

Part 4 - Count with Or Criteria1:47 min

"If not using an array formula, it could be rather difficult to count how many
times “or” criteria are satisfied. For example, we may want to count on how
many days it was raining or snowing. This session covers how to use an array
formula to count instances when “or” criteria hold true. After completing this
session, you will be able to count how many times “or” criteria are satisfied.
Learning objectives; • Find whether “or” criteria are satisfied in each particular
row • Sum the result for each row to count how many times the criteria are
satisfied • Achieve the same result by setting up an array function"

What criteria are we counting in our sample using an array function?

a. People with high income or a bachelor's degree

b. People with high income or a PhD degree
c. People with low income or a PhD degree

When setting up an array formula in Excel to count values with OR criteria, what
does pressing Control, Shift, and Enter do?

a. It applies the formula to each cell individually.

b. It evaluates the formula as a single condition.
c. It finalizes the array formula and applies it across the entire array of
data.

In Excel, how do you set up an array formula to count values based on OR

criteria?

a. Use the IF function with specific conditions and press Enter.

b. Use the SUMIFS function with multiple conditions.
c. Use the IF function with array references and press Control, Shift, and
Enter.

Part 5 - Sum Every Nth Row 1:10 min

"Sometimes we may need to find the sum of every nth row in our dataset. For
example, we may have daily revenues and look for the sum of Saturday
revenues. Then we would need to sum every 7th row. This session covers
 how to sum every nth row in Excel. After completing this session, you will be
able to count every nth row by setting up an appropriate array function.
Learning objectives; • Set up ROW and MOD functions to distinguish relevant
rows • Allocate the logical value of TRUE to every nth row and FALSE to all
other rows • Find the sum of every nth row using an array function"

Which function returns the number of the row in Excel?

a. COLUMN
b. ROW
c. INDEX

Which Excel function can be used to find the remainder of the division of one
number by another?

a. MOD()
b. ROUND()
c. CEILING()

 The Excel function that can be used to find the remainder of the division of one number by another
is MOD().
 Here’s a brief overview of the functions you mentioned:
 MOD(): Returns the remainder after a number is divided by a divisor.
 ROUND(): Rounds a number to a specified number of digits.
 CEILING(): Rounds a number up to the nearest multiple of a specified value.
 So, for finding the remainder, use MOD().

How do you set up an array formula in Excel to sum values from every fourth
row?

a. Use the SUM() function with a condition

b. Use the IF() function combined with the MOD() function and press Enter
c. Use the SUMPRODUCT() function with the MOD() function and press
Control, Shift, and Enter

 Here’s how you can do it:

 Formula Example:
 =SUMPRODUCT((MOD(ROW(A1:A100), 4) = 0) * A1:A100)
  Explanation:
 ROW(A1:A100): Generates an array of row numbers.
 MOD(..., 4) = 0: Checks if the row number is a multiple of 4.
 SUMPRODUCT(): Multiplies the condition (TRUE/FALSE) by the values in the range and sums
them.
 Entering the Formula:
 After typing the formula, press Control + Shift + Enter to enter it as an array formula.

Part 6 - Sum Largest Numbers 1:57 min

"It could be useful to find the sum of several largest numbers. In order to do
so, we need to set up an array formula. This session looks at how to sum the
largest numbers in Excel. After completing this session, you will be able to
build an array formula counting the required number of the largest numbers.
Learning objectives; • Use the LARGE function to find the largest and the
second largest number • Modify the second argument of the LARGE function
to return several largest numbers • Set up an array formula to find the sum of
several largest numbers"

Which function is used to find the largest numbers in an array?

a. MAX
b. LARGE
c. SUM

 To sum a range with errors using the IFERROR() function, you should:
 Wrap the IFERROR() function around the SUM() function.
 Example:
 excel
 Copy
 =IFERROR(SUM(A1:A10), 0)
 In this example, if the SUM of the range A1:A10 results in an error, the formula will return 0 instead
of an error message. This approach effectively handles any errors that might occur during the
summation.

How do you specify multiple values to retrieve using the LARGE() function?

a. By using commas
b. By using figure brackets
c. By using parentheses

 To sum a range with errors using the IFERROR() function, you should:
  Wrap the IFERROR() function around the SUM() function.
 Example:
 =IFERROR(SUM(A1:A10), 0)
 In this example, if the SUM of the range A1:A10 results in an error, the formula will return 0 instead
of an error message. This approach effectively handles any errors that might occur during the
summation.

What is the final step to correctly calculate the sum of the three largest numbers
using an array formula?

a. Press Enter
b. Press Control, Shift, and Enter
c. Press Alt, Shift, and Ente

Part 7 - Sum Range with Errors1:02 min

"If our data contains errors, it is not possible to sum values using the standard
SUM function as it will result in an error. This session covers how to correctly
sum a range of data with errors. After completing this session, you will be able
to set up an array function to sum a range containing errors. Learning
objectives; • Experience an error after summing the range with errors using
the SUM function • Apply the IFERROR function to return zero if a cell
contains an error • Set up an array formula to find the sum of a range with
errors"

What function allows us to specify a value to use if there's an error in a cell in

Excel?

a. IFERROR
b. SUMIF
c. ERRORVALUE

What should you press to correctly execute the formula as an array formula?

a. Enter
b. Control, Shift and Enter
c. Alt, Shift and Enter

Sum Range with Errors

How should the IFERROR() function be used to sum a range with errors?
a. Wrap the IFERROR() function around the SUM() function
b. Wrap the SUM() function around the IFERROR() function
c. Use the IFERROR() function with each individual cell separately

To sum a range with errors using the IFERROR() function, you should:

Wrap the IFERROR() function around the SUM() function.

Example:

excel

Copy

=IFERROR(SUM(A1:A10), 0)

In this example, if the SUM of the range A1:A10 results in an error, the formula will return 0 instead of
an error message. This approach effectively handles any errors that might occur during the summation.

Part 8 - Sum with Or Criteria2:45 min

"If not using an array formula, it could be rather difficult to sum values for
which “or” criteria are satisfied. For example, we may only want to sum the
hours of sunshine on the days when it was raining or snowing. This session
covers how to use an array formula to only sum values for which “or” criteria
hold true. After completing this session, you will be able to sum values for
which “or” criteria are satisfied. Learning objectives; • Find whether “or” criteria
are satisfied in each particular row • Sum the result for each row, in which the
criteria are satisfied, to find the total value • Achieve the same result by setting
up an array function"

Which function is used to check if a condition is satisfied in Excel?

a. COUNTIF
b. IF
c. SUMIF

What is the purpose of multiplying the result of the IF function by the number of
homes owned?

a. To count the number of criteria satisfied

b. To discard homes owned by people who don't meet the criteria
c. To add a constant value to each row

What should you press to correctly execute the array formula?

a. Enter
b. Control, Shift, and Enter
c. Alt, Shift, and Enter