Excel 2016 Advanced Formulas Mastery

What function would you use to calculate the average of a range of cells in Excel 2016?

• SUM

• AVERAGE

• COUNT

• MEDIAN

Which function can be used to count the number of cells in a range that contain numbers?

• COUNT

• COUNTA

• COUNTBLANK

• COUNTIF

Match the following

• UPPER

• The ___ function allows you to convert text to uppercase (all capital letters)

• IF

• function adds cells in a range that meet multiple criteria

• SUMIF

• function converts all uppercase letters in a text string to lowercase

• LOWER

• .Which of the following functions returns a value if all conditions are met?

• CONCATENATE

• In this case, you combine the first and last names into two different formats for future mail merges.

Which function would you use to find the largest number in a range of cells?

• MAX

• LARGE

• HIGHEST

• TOP

What does the IF function do in Excel 2016?

• Checks whether a condition is met, and returns one value if TRUE, and another if FALSE

• Increments a number by a specified value

• Filters a range of data based on criteria

• Finds the internal rate of return for a series of cash flows

Which of the following is not a logical function in Excel 2016?

• AND

• OR

• NOT

• CONCATENATE

The ​ (a)   function counts the number of cells in a given range that meet a specific condition.

• COUNTIF
• COUNT
• COUNTIFS
• SUMIF
• SUMIFS
• ADD

What is the purpose of the VLOOKUP function in Excel 2016?

• To check the validity of data in a cell

• To look up and retrieve data from a specific column in a table

• To filter data based on specified criteria

• To create a vertical histogram

How would you sum cells A1 through A10 only if they are greater than 50?

• SUM(A1:A10)

• SUMIF(A1:A10, ">50")

• SUM(A1:A10, ">50")

• A1:A10>50

Benjamin, Noah, and Elijah are working on a group project in Excel 2016. They each have written a part of the project report and now they need to join these parts together in one cell. Which function should they use?

• The SPLIT function, which splits a text string into separate cells

• The CONCATENATE function, which joins several text strings into one text string

• The UPPER function, which converts text to uppercase

• The SEARCH function, which searches for a text string within another text string