MCAS Formulas, Functions & Calculations 2 — Questions and Answers
Question 1: In Excel, what error value appears when a formula attempts to divide a number by zero?
- #VALUE!
- #REF!
- #DIV/0! (Correct answer)
- #NULL!
Correct answer: #DIV/0!
#DIV/0! is the error Excel displays whenever a formula tries to divide by zero or an empty cell.
Question 2: Which Excel function is used to look up a value in the first column of a table and return a value in the same row from another column?
- HLOOKUP
- VLOOKUP (Correct answer)
- INDEX
- MATCH
Correct answer: VLOOKUP
VLOOKUP searches the first column of a range vertically and returns a value from a specified column in the same row.
Question 3: In Excel, what is the correct syntax to calculate 15% of the value in cell B2?
- =B2/15%
- =B2*15% (Correct answer)
- =15%+B2
- =PERCENT(B2,15)
Correct answer: =B2*15%
Multiplying the cell value by 15% (which Excel treats as 0.15) calculates the percentage of that value.
Question 4: What does the Excel function =TODAY() return?
- The date and time when the workbook was first created
- The current date, updated automatically each time the workbook is opened or recalculated (Correct answer)
- The date of the last time the file was saved
- A static date that was entered when the function was first used
Correct answer: The current date, updated automatically each time the workbook is opened or recalculated
=TODAY() is a volatile function that returns the current system date and updates every time the workbook recalculates.
Question 5: In Excel, what does the CONCATENATE function (or the & operator) do?
- Adds two numbers together
- Joins two or more text strings into one string (Correct answer)
- Compares two cells and returns TRUE if they match
- Converts a number to text format
Correct answer: Joins two or more text strings into one string
CONCATENATE and the & operator both join (combine) multiple text strings into a single combined text string.
Question 6: In Excel, what is the difference between COUNT and COUNTA?
- COUNT sums values; COUNTA finds averages
- COUNT counts only numeric cells; COUNTA counts all non-empty cells regardless of type (Correct answer)
- COUNT counts visible cells; COUNTA counts all cells including hidden rows
- COUNT includes blank cells; COUNTA excludes blank cells
Correct answer: COUNT counts only numeric cells; COUNTA counts all non-empty cells regardless of type
COUNT only tallies cells containing numbers, while COUNTA counts any non-empty cell including text, dates, and numbers.
MCAS Formulas, Functions & Calculations 2
In Excel, what error value appears when a formula attempts to divide a number by zero?