Advanced Formulas and Functions – Excel MCQs

  1. Which function would you use to find the largest value in a range?
    a) MAX
    b) MIN
    c) LARGE
    d) SUM
    Answer: a) MAX
  2. What does the VLOOKUP function do in Excel?
    a) Looks up data vertically in a table
    b) Looks up data horizontally in a table
    c) Calculates the sum of a range
    d) Counts the number of cells that meet criteria
    Answer: a) Looks up data vertically in a table
  3. Which function calculates the standard deviation of a dataset?
    a) AVERAGE
    b) STDEV
    c) VAR
    d) MEDIAN
    Answer: b) STDEV
  4. How does the IF function work in Excel?
    a) It calculates the sum of values
    b) It returns one value if a condition is true and another if it is false
    c) It sorts data in a range
    d) It finds the minimum value in a range
    Answer: b) It returns one value if a condition is true and another if it is false
  5. What function would you use to count the number of cells in a range that contain numbers?
    a) COUNTIF
    b) COUNTA
    c) COUNT
    d) COUNTBLANK
    Answer: c) COUNT
  6. Which function can find the position of a character in a text string?
    a) FIND
    b) SEARCH
    c) MID
    d) LEFT
    Answer: a) FIND and b) SEARCH
  7. What is the purpose of the CONCATENATE function in Excel?
    a) To add numbers together
    b) To multiply numbers
    c) To join text strings together
    d) To divide numbers
    Answer: c) To join text strings together
  8. Which function would you use to calculate the average of a range of numbers, ignoring any text values?
    a) AVERAGEIF
    b) AVERAGE
    c) MEDIAN
    d) AVERAGEA
    Answer: b) AVERAGE
  9. What does the INDEX function do in Excel?
    a) Finds the position of a value in a range
    b) Returns a value from a specified position in a range
    c) Counts the number of cells that meet a criteria
    d) Sorts data in ascending order
    Answer: b) Returns a value from a specified position in a range
  10. Which function would you use to round a number to a specified number of decimal places?
    a) ROUND
    b) ROUNDUP
    c) ROUNDDOWN
    d) All of the above
    Answer: d) All of the above
  11. How does the MATCH function work in Excel?
    a) Returns the value from a specified position in a range
    b) Returns the relative position of an item in a range that matches a specified value
    c) Finds the largest value in a range
    d) Concatenates text strings
    Answer: b) Returns the relative position of an item in a range that matches a specified value
  12. What function would you use to sum a range of values that meet a specific condition?
    a) SUM
    b) SUMIF
    c) SUMPRODUCT
    d) SUMIFS
    Answer: b) SUMIF
  13. Which function can be used to return the current date and time?
    a) TODAY
    b) NOW
    c) DATE
    d) TIME
    Answer: b) NOW
  14. How can you calculate the monthly payment for a loan using Excel?
    a) SUM function
    b) PMT function
    c) PV function
    d) FV function
    Answer: b) PMT function
  15. Which function would you use to look up a value in a table based on matching criteria in both rows and columns?
    a) VLOOKUP
    b) HLOOKUP
    c) INDEX-MATCH
    d) LOOKUP
    Answer: c) INDEX-MATCH
  16. What is the purpose of the OFFSET function in Excel?
    a) To return the value of a cell offset from a given reference
    b) To find the position of a value in a range
    c) To count the number of cells that meet a criteria
    d) To round numbers to a specified number of decimal places
    Answer: a) To return the value of a cell offset from a given reference
  17. Which function would you use to extract the year from a date?
    a) DAY
    b) MONTH
    c) YEAR
    d) DATE
    Answer: c) YEAR
  18. How does the TEXT function work in Excel?
    a) Converts a value to text in a specified format
    b) Finds the position of a character in a text string
    c) Joins text strings together
    d) Extracts a substring from a text string
    Answer: a) Converts a value to text in a specified format
  19. Which function allows you to perform a logical test and return one value for a TRUE result, and another for a FALSE result?
    a) AND
    b) OR
    c) IF
    d) NOT
    Answer: c) IF
  20. What function would you use to sum the products of corresponding ranges or arrays?
    a) SUM
    b) PRODUCT
    c) SUMIF
    d) SUMPRODUCT
    Answer: d) SUMPRODUCT

Related Posts: