Microsoft Excel Formulas Questions and Answers 2 — Questions and Answers

Question 1: What does the SUMPRODUCT function do in Excel?

Multiplies corresponding elements in arrays and returns the sum of those products (Correct answer)
Adds all products in a product catalog
Calculates the sum and product separately
Multiplies all values in a single range

Correct answer: Multiplies corresponding elements in arrays and returns the sum of those products

SUMPRODUCT multiplies corresponding elements in two or more arrays (ranges of the same size) and then sums those products, making it extremely versatile for conditional calculations.

SUMPRODUCT is one of Excel's most powerful functions. Given arrays like {1,2,3} and {4,5,6}, it calculates (1*4)+(2*5)+(3*6)=32. Beyond simple multiplication, it can perform conditional calculations without being an array formula. For example, SUMPRODUCT((A1:A10="East")*(B1:B10>100)*(C1:C10)) sums values in column C where column A is "East" and column B exceeds 100. This made it a popular alternative to SUMIFS before SUMIFS was introduced.

Question 2: Which formula correctly calculates the percentage change between an old value in A1 and a new value in B1?

=(B1-A1)/A1 (Correct answer)
=(A1-B1)/B1
=B1/A1*100
=(B1+A1)/A1

Correct answer: =(B1-A1)/A1

The percentage change formula is (New-Old)/Old, which translates to (B1-A1)/A1 in Excel. Format the result as a percentage.

The standard percentage change formula is (New Value - Old Value) / Old Value. In Excel, with the old value in A1 and the new value in B1, this becomes =(B1-A1)/A1. The result is a decimal that should be formatted as a percentage. For example, if A1=100 and B1=125, the formula returns 0.25, which displays as 25% when formatted. Be careful when the old value is zero, as this causes a division by zero error. Use IFERROR to handle such cases.

Question 3: What is the result of the formula =MOD(17,5)?

2 (Correct answer)
3
5
17

Correct answer: 2

MOD returns the remainder after division. 17 divided by 5 is 3 with a remainder of 2, so MOD(17,5) returns 2.

The MOD function returns the remainder of a division operation. MOD(17,5) divides 17 by 5 (which equals 3 with remainder 2) and returns 2. MOD is frequently used in Excel for tasks like determining if a number is even or odd (MOD(n,2)=0 means even), creating alternating row colors in conditional formatting, or cycling through a repeating pattern. It follows the formula: MOD(n,d) = n - d*INT(n/d).

Question 4: Which formula would you use to combine the text in cells A1 and B1 with a space between them?

=A1&" "&B1 (Correct answer)
=A1+" "+B1
=JOIN(A1," ",B1)
=MERGE(A1,B1)

Correct answer: =A1&" "&B1

The ampersand (&) operator concatenates text strings in Excel. A1&" "&B1 joins the contents of A1 and B1 with a space character between them.

The ampersand (&) is Excel's concatenation operator. =A1&" "&B1 joins the value in A1, a literal space, and the value in B1. Alternatively, you can use the CONCATENATE function: =CONCATENATE(A1," ",B1) or in newer versions, the CONCAT function. The TEXTJOIN function is even more powerful for joining multiple cells with a delimiter. Note that the plus (+) operator performs addition, not text joining, and would return an error if the cells contain text.

Question 5: What does the ROUND function do when the second argument is negative, such as =ROUND(1567,-2)?

Rounds to the nearest hundred, returning 1600 (Correct answer)
Returns an error
Rounds to 2 decimal places, returning 1567.00
Removes the last two digits, returning 1500

Correct answer: Rounds to the nearest hundred, returning 1600

When the num_digits argument is negative, ROUND rounds to the left of the decimal point. -2 rounds to the nearest hundred, so 1567 becomes 1600.

ROUND with a negative second argument rounds to the left of the decimal point. ROUND(1567,-1) rounds to the nearest 10 (1570), ROUND(1567,-2) rounds to the nearest 100 (1600), and ROUND(1567,-3) rounds to the nearest 1000 (2000). This is useful in financial reporting where you need to present figures rounded to thousands or millions. Related functions include ROUNDUP (always rounds away from zero) and ROUNDDOWN (always rounds toward zero), which also accept negative arguments.

Question 6: Which formula returns the number of days between two dates in cells A1 and B1?

=B1-A1 (Correct answer)
=DAYS(A1,B1)
=DATEDIF(A1,B1,"D")
=NETWORKDAYS(A1,B1)

Correct answer: =B1-A1

Simply subtracting one date from another (=B1-A1) returns the number of days between them, since Excel stores dates as serial numbers.

In Excel, dates are stored as serial numbers (January 1, 1900 = 1), so subtracting one date from another directly gives the number of days between them. =B1-A1 returns a positive number if B1 is the later date. While DATEDIF and DAYS360 also calculate date differences, simple subtraction is the most straightforward method for calendar days. NETWORKDAYS excludes weekends and holidays, making it different from a simple day count. Format the result cell as Number (not Date) to see the numeric day count.

Microsoft Excel Formulas Questions and Answers 2

What does the SUMPRODUCT function do in Excel?