Microsoft Excel Formulas 4 — Questions and Answers
Question 1: What does =TRIM(" Hello World ") return?
- " Hello World "
- HelloWorld
- Hello World (Correct answer)
- Hello World
Correct answer: Hello World
TRIM removes leading and trailing spaces and collapses multiple internal spaces to a single space.
Question 2: Which function returns the largest value in a range?
- LARGE(range,1)
- MAXIMUM(range)
- MAX(range)
- Both A and C (Correct answer)
Correct answer: Both A and C
Both MAX(range) and LARGE(range,1) return the largest value; MAX is the direct function while LARGE(range,1) specifies the 1st largest.
Question 3: What is the result of =TEXT(1234.5, "$#,##0.00")?
- 1234.5
- $1,234.50 (Correct answer)
- #,##0.00
- $1234.5
Correct answer: $1,234.50
TEXT converts a number to a formatted text string; the format code "$#,##0.00" produces "$1,234.50".
Question 4: How do you reference the entire column B in a formula?
- B:B (Correct answer)
- B1:B99999
- COL(B)
- $B
Correct answer: B:B
Using B:B selects all cells in column B as a dynamic range reference.
Question 5: Which formula counts cells in A1:A20 that contain values greater than 50?
- =COUNT(A1:A20,">50")
- =COUNTIF(A1:A20,">50") (Correct answer)
- =SUMIF(A1:A20,">50")
- =COUNTIFS(A1:A20,50)
Correct answer: =COUNTIF(A1:A20,">50")
COUNTIF counts cells matching a single criterion; ">50" selects cells with values greater than 50.
Question 6: What does the TRANSPOSE function do when entered as an array formula?
- Sorts data alphabetically
- Converts rows to columns and columns to rows (Correct answer)
- Removes duplicate values
- Reverses the order of values
Correct answer: Converts rows to columns and columns to rows
TRANSPOSE swaps the orientation of a range, turning rows into columns and vice versa.
Question 7: Which formula returns the number of characters in the text string in cell A1?
- =SIZE(A1)
- =LENGTH(A1)
- =LEN(A1) (Correct answer)
- =COUNT(A1)
Correct answer: =LEN(A1)
LEN returns the number of characters in a text string, including spaces.
Microsoft Excel Formulas 4
What does =TRIM(" Hello World ") return?