2.7 Excel multiple-choice questions
In some countries, the comma (,) is used as a decimal separator, and the semicolon (;) is used as a list separator. If you are using Excel in a country where the comma is used as a decimal separator, you may need to use the semicolon as a list separator in Excel formulas.
By default, Excel for Windows uses the list separator in Windows regional settings. Excel's function tooltip shows the separator expected by your system while you type a formula. The Use system separators option under File > Options > Advanced controls decimal and thousands separators, not the formula list separator.
In the examples below, we use a comma as the list separator. If you copy a formula into an installation that expects semicolons, replace the commas between function arguments with semicolons. Colons still denote cell ranges, such as A2:A6.
2.7.M1 Relative reference copying
You are copying cell C2 to cell C3 (i.e., select C2, press Ctrl+C, then select C3, and press Ctrl+V). Cell C2 has a formula =A2+B2. After copying, what will the formula in cell C3 become?
=A3+B3=$A$2+$B$2=A2+B2=$A2+$B2
2.7.M2 Mixed reference copying
Which formula keeps column A fixed when copied across and allows the row number to change when copied down?
=$A2=A$2=$A$2=A2
2.7.M3 Mixed reference copying 1
You enter the formula =B2*$C$1 in cell D2 and drag it down using the fill handle ✚. The multiplier $C$1 must stay fixed, while B2 must update. What will the formula in cell D3 be?
=B3*C1=B2*$C$1=B3*$C$1=B3*$C1
2.7.M4 Mixed reference copying 2
You copy the formula =$A2+B$1 from cell C2 to D3. What will the new formula in D3 be?
=$A3+C$1=$A2+B$1=$A3+B$1=A2+B$1
2.7.M5 Relative and absolute reference
You want to create a formula that multiplies the values in column A by a constant in cell B1. If you want to copy the formula down without changing the reference to B1, which formula should you use in cell C2?
=A2*B1=$A2*B1=A2*$B$1=A2*$B1
2.7.M6 Classifying grades
You have a list of student grades in column A, where A2 contains the value 85. You want to classify grades into letter grades as follows:
- A: 90 and above
- B: 80 or above and below 90
- C: 70 or above and below 80
- D: Below 70
Which formula would you use in cell B2 to assign the correct letter grade based on the numeric grade in A2?
=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "D")))=IF(A2>=90, "A", IF(A2>=80, "C", IF(A2>=70, "B", "D")))=IF(A2<=90, "A", IF(A2<=80, "B", IF(A2<=70, "C", "D")))=IF(A2=90, "A", IF(A2=80, "B", IF(A2=70, "C", "D")))
2.7.M7 Using constants in formulas
You are calculating the total cost of products based on their price and the number of units sold. The prices of the products are listed in column A, the number of units sold is listed in column B, and you want to multiply the price by the units sold in column C. You also want to apply a fixed discount of 10% from cell D2 to all the totals. Your task is to write a formula that you can drag down to calculate the total cost for each product after the discount is applied.
Inspect the price, quantity, and fixed discount cells in Table 1 before choosing a formula.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | Units Sold | Total Cost | Discount |
| 2 | 10.00 | 5 | 10% | |
| 3 | 15.00 | 3 | ||
| 4 | 12.00 | 4 | ||
| 5 | 8.00 | 6 |
You need to calculate the total cost for each row as Total Cost = Price * Units Sold * (1 - Discount).
Which formulas can you use in C2 and drag down to calculate the total cost for each product, applying the fixed discount from D2? (Multiple answers may be correct.)
=A2 * B2 * (1 - D2)=A2 * B2 * (1 - $D$2)=A$2 * B$2 * (1 - D$2)=$A2 * $B2 * (1 - D$2)
2.7.M8 Using AutoFill with a gap in the data
On an otherwise empty worksheet, you have the following range (not an Excel Table) in column A, where there is a gap in row 5. You want to auto-fill a sequence of numbers starting from 1 in cell B2. You enter 1 in cell B2 and 2 in cell B3. You then select both cells and double-click the fill handle to auto-fill down the column.
Inspect the gap in column A of Table 2 before predicting how far AutoFill will continue.
| A | B | |
|---|---|---|
| 1 | Data | Sequence |
| 2 | Alice Johnson | 1 |
| 3 | Bob Smith | 2 |
| 4 | Charlie Rogers | |
| 5 | ||
| 6 | Diana Stone |
AutoFill fills cells with a series based on the pattern in the selected cells.
When you point to the fill handle (a small square at the bottom-right corner of a cell), the cursor changes to a black cross. Double-clicking the fill handle will auto-fill the cells based on the pattern detected.
What values will be filled in cells B4 to B6 when you double-click the fill handle?
- 1, 1, 1
- 3, 4, 5
- 1, 2, 1
- 3, blank, blank