MCQs on Formulas and Basic Functions | Excel MCQ Questions
Formulas and Basic Functions are fundamental in spreadsheet applications like Google Sheets or Excel. This chapter covers essential topics such as basic arithmetic functions (Sum, Average, Min, Max), referencing methods, logical and text functions, and error handling functions. Mastering these functions is key to efficiently analyzing and manipulating data.
Introduction to Formulas (Sum, Average, Min, Max)
1. Which function in Excel or Google Sheets is used to calculate the sum of a range of numbers?
a) AVERAGE
b) MIN
c) MAX
d) SUM
2. What does the AVERAGE function return?
a) The highest value in a range
b) The lowest value in a range
c) The sum of all values in a range
d) The arithmetic mean of all values in a range
3. Which function would you use to find the minimum value in a set of data?
a) MIN
b) SUM
c) COUNT
d) AVERAGE
4. If you need the highest value in a dataset, which function would you use?
a) MAX
b) MIN
c) SUM
d) AVERAGE
5. How would you calculate the total value of a column that includes numbers from A1 to A10?
a) =AVERAGE(A1:A10)
b) =SUM(A1:A10)
c) =MAX(A1:A10)
d) =MIN(A1:A10)
Understanding Relative and Absolute References
6. What is a relative reference in a formula?
a) A reference that stays constant when copied
b) A reference that changes when the formula is copied to another cell
c) A reference to a cell in another workbook
d) A reference to a range of cells
7. Which of the following is an example of an absolute reference?
a) A1
b) $A$1
c) A$1
d) $A1
8. What does the dollar sign ($) signify in cell references?
a) It makes the reference absolute
b) It makes the reference relative
c) It makes the reference volatile
d) It increases the value
9. When copying a formula that uses a relative reference, what happens to the cell reference?
a) It remains constant
b) It changes according to the new location
c) It causes an error
d) It becomes an absolute reference
10. If you want a cell reference to remain fixed when copying a formula across different cells, you should use:
a) Relative reference
b) Mixed reference
c) Absolute reference
d) Indirect reference
Logical Functions (IF, AND, OR)
11. What does the IF function do?
a) Performs arithmetic operations
b) Displays text in a cell
c) Returns one value if a condition is true and another value if false
d) Combines text from different cells
12. Which of these functions allows you to check multiple conditions at once?
a) IFERROR
b) AND
c) CONCAT
d) LEN
13. What does the AND function return?
a) TRUE if any condition is met
b) TRUE if all conditions are met
c) FALSE if any condition is met
d) FALSE if all conditions are met
14. How does the OR function differ from the AND function?
a) OR returns TRUE if all conditions are met
b) OR returns TRUE if any of the conditions are met
c) OR returns FALSE if all conditions are met
d) OR cannot handle more than two conditions
15. In the formula =IF(A1>10, "Yes", "No"), what will the function return if A1 is greater than 10?
a) “No”
b) TRUE
c) “Yes”
d) 10
Text Functions (CONCAT, LEFT, RIGHT, LEN)
16. Which function is used to combine the contents of two or more cells into one cell?
a) CONCAT
b) LEFT
c) RIGHT
d) MID
17. What does the LEFT function do?
a) Returns the first N characters from a text string
b) Returns the last N characters from a text string
c) Removes leading spaces from a text string
d) Changes all text to lowercase
18. What is the purpose of the RIGHT function?
a) Extracts characters from the beginning of a string
b) Returns the last N characters of a text string
c) Converts text to uppercase
d) Counts the number of characters in a string
19. What does the LEN function return?
a) The length of a text string
b) The last character in a text string
c) The number of words in a text string
d) The number of cells in a range
20. Which of the following formulas will combine the text in cells A1 and B1 with a space between them?
a) =CONCAT(A1, ” “, B1)
b) =TEXTJOIN(A1, ” “, B1)
c) =CONCATENATE(A1, B1)
d) =A1 + ” ” + B1
Error Handling Functions (IFERROR)
21. What does the IFERROR function do?
a) It returns an error message if the formula results in an error
b) It automatically corrects errors in formulas
c) It hides errors from appearing in a cell
d) It stops the formula from calculating
22. How is the IFERROR function used in a formula?
a) =IFERROR(value, value_if_error)
b) =IFERROR(value, value_if_non_error)
c) =IFERROR(value)
d) =IFERROR(value, “”)
23. What is the result of =IFERROR(1/0, "Error")?
a) “1”
b) “Error”
c) “Infinity”
d) #DIV/0!
24. Which of the following would cause an error in a formula that uses the IFERROR function?
a) Division by zero
b) Text entered into a number field
c) Referencing an empty cell
d) All of the above
25. What will =IFERROR(A1/B1, "Invalid Division") return if B1 is 0?
a) “Invalid Division”
b) 0
c) “Error”
d) #DIV/0!
General Functions & Use Cases
26. Which function is used to find the maximum value in a dataset?
a) MIN
b) AVERAGE
c) MAX
d) COUNT
27. How would you correct a formula if a cell contains an error like #VALUE!?
a) Remove the cell reference
b) Use the IFERROR function
c) Change the data type
d) Ignore the error
28. Which function is used to check if a number is even or odd?
a) MOD
b) IF
c) ROUND
d) ODD
29. Which function would you use to extract a portion of text from a string?
a) MID
b) LEFT
c) RIGHT
d) CONCAT
30. What does the CONCATENATE function do?
a) Joins text from two or more cells into one cell
b) Extracts specific text from a string
c) Replaces text within a string
d) Counts the number of characters in a string
Answer Key
| QNo | Answer |
|---|---|
| 1 | d) SUM |
| 2 | d) The arithmetic mean of all values in a range |
| 3 | a) MIN |
| 4 | a) MAX |
| 5 | b) =SUM(A1:A10) |
| 6 | b) A reference that changes when the formula is copied to another cell |
| 7 | b) $A$1 |
| 8 | a) It makes the reference absolute |
| 9 | b) It changes according to the new location |
| 10 | c) Absolute reference |
| 11 | c) Returns one value if a condition is true and another value if false |
| 12 | b) AND |
| 13 | b) TRUE if all conditions are met |
| 14 | b) OR returns TRUE if any of the conditions are met |
| 15 | c) “Yes” |
| 16 | a) CONCAT |
| 17 | a) Returns the first N characters from a text string |
| 18 | b) Returns the last N characters of a text string |
| 19 | a) The length of a text string |
| 20 | a) =CONCAT(A1, ” “, B1) |
| 21 | a) It returns an error message if the formula results in an error |
| 22 | a) =IFERROR(value, value_if_error) |
| 23 | b) “Error” |
| 24 | d) All of the above |
| 25 | a) “Invalid Division” |
| 26 | c) MAX |
| 27 | b) Use the IFERROR function |
| 28 | a) MOD |
| 29 | a) MID |
| 30 | a) Joins text from two or more cells into one cell |