TestOut Excel 5.7 Using Advanced Functions

David is working on a budget spreadsheet in Excel and wants to ensure that a specific cell reference remains constant when copying the formula to other cells. Which symbol should David use to create an absolute reference in Excel formulas?

• %

• #

• $

• &

What is a mixed reference in Excel?

• A reference that changes both rows and columns

  $A1 or A$1

• A reference that is always absolute

  $A$1 only

• A reference that is part absolute and part relative

  $A1 or A$1

• A reference that only changes rows

  $A1 or A$1

Grace is working on her Excel assignment and needs to lock a cell reference to ensure it doesn't change when she copies the formula to other cells. What key can she press to add $ absolute to a cell reference?

• F4 Function Key

• F6 Function Key

• F8 Function Key

• F10 Function Key

Isla is managing a small store and wants to calculate the total sales for products that meet a certain condition, such as being in a specific category. The formula she should use to add values in a specified range that meet this condition is called:

• SUMIFS

• SUMIF

• COUNTIF

• SUBTOTAL

Daniel is organizing a book club and wants to count the number of books that belong to a specific genre. Which function should he use?

• Sumif

• Productif

• Averageif

• Countif

Noah is learning to code and wants to write a program to calculate the area of a rectangle. Which symbol should he use to multiply the length and width?

• $

• &

• .

• *

James is analyzing his class's test scores and wants to count the number of students who scored more than 70 in cells B4:M4. What function should he use?

• COUNTIF

• COUNTA

• COUNTBLANK

• COUNTIFS

Mason is organizing a list of his favorite books and wants to ensure each title appears only once. He has listed the titles in cells A4:A18. What function should Mason use in cell G4 to generate a list of unique book titles?

• UNIQUE

• FILTER

• SORT

• INDEX

Maya is working on a spreadsheet to analyze her monthly expenses. She wants to find out the largest expense in a range of cells. Which function would she use?

• MIN

• MAX

• SUM

• AVERAGE

Anika is working on her monthly budget in Excel. She wants to find out the average amount she spends on groceries each week. Which function should she use to calculate the average of her weekly grocery expenses?

• SUM

• AVERAGE

• COUNT

• MEDIAN