D388 Fundamentals of Spreadsheets and Data Presentations - Set 3 - Part 2

Correct Answer: =STDEV(B2:B30)

STDEV calculates the standard deviation based on a sample of a population, measuring how spread out the values are around the mean. A larger standard deviation indicates greater variability. STDEV is the sample version; STDEVP (or STDEV.P) is used when the data represents the entire population.

Correct Answer: =SUM(B1:B3)

Because A1:A3 uses relative references (no dollar signs), copying the formula one column to the right shifts the column letters accordingly. Moving from column A to column B changes A1:A3 into B1:B3. The rows stay the same because the copy moved horizontally, not vertically.

Correct Answer: The range (spread) between the highest and lowest values

MAX returns the largest value and MIN returns the smallest value in a range. Subtracting the minimum from the maximum yields the statistical range, which measures the total spread of the data. This is a common way to gauge variability quickly, though it only considers the two extreme values.

Correct Answer: =A1*1.08

Multiplying by 1.08 keeps the original amount (the 1) and adds 8% (the 0.08), giving the price plus tax in one step. Multiplying by only 0.08 would return just the tax amount, and adding 8 would incorrectly add a flat 8 dollars rather than a percentage.

Correct Answer: Converts the value in A1 to text formatted with two decimal places

The TEXT function converts a numeric value to text using a specified format code. The code 0.00 formats the number with exactly two decimal places, so a value like 5.1 becomes the text string 5.10. This is useful for displaying numbers consistently or combining them with other text.

Correct Answer: The lookup value was not found in the lookup range

The #N/A error means not available and commonly appears in lookup functions like VLOOKUP, HLOOKUP, or MATCH when the lookup value cannot be found in the specified range. It can also result from mismatched data types or extra spaces. Wrapping the formula in IFERROR is a typical way to display a friendlier message.

Correct Answer: 10

AVERAGE sums the arguments and divides by the count of numeric values. Here 4+8+12+16 equals 40, and there are four values, so 40 divided by 4 equals 10. AVERAGE ignores empty cells and text but includes every numeric value supplied.

Correct Answer: $A$1

$A$1 is an absolute reference with dollar signs locking both the column and the row. This means the reference stays fixed on cell A1 regardless of whether the formula is copied across columns or down rows. The mixed references A$1 and $A1 lock only one dimension, while A1 (relative) changes freely.

Correct Answer: The number of cells in A1:A20 containing the text Yes

COUNTIF counts cells that match a specified criterion. With the text criterion Yes, it returns how many cells in A1:A20 contain that exact text (the match is not case-sensitive). This is commonly used to tally categorical responses such as yes/no survey answers.

Correct Answer: 3

MATCH returns the relative position of a lookup value within a range, not the value itself. Since C is the third item in the range A1:A5, MATCH returns 3. The third argument 0 specifies an exact match. MATCH is frequently paired with INDEX to build flexible lookups.

Correct Answer: 25

Parentheses are evaluated first, so 6+4 equals 10. Then, because division and multiplication share the same precedence, Excel evaluates them left to right: 10/2 equals 5, and 5*5 equals 25. Failing to work left to right, or evaluating multiplication before division, would give an incorrect answer.

Correct Answer: The top level is the primary sort, and each subsequent level only breaks ties within the level above it

In a multi-level sort, Excel applies the levels from top to bottom in the Sort dialog. The first (top) level is the primary sort key; the second level acts as a tiebreaker only for rows that share the same value in the first level, and so on. This lets you sort, for example, by Department first and then by Last Name within each department.

Correct Answer: A custom list defined in Excel Options, selected in the Sort dialog's Order box

Excel includes built-in custom lists for months and weekdays, and you can create your own. In the Sort dialog, choosing Custom List in the Order drop-down lets you sort by that logical sequence instead of alphabetically, so months appear January through December rather than April, August, December, etc.

Correct Answer: A filter is currently applied to that column, hiding some rows

When a filter is active on a column, the standard drop-down arrow changes to a funnel-shaped icon. This visual cue tells the user that not all rows are being displayed because criteria are filtering the data. Clicking the funnel and choosing Clear Filter restores all rows for that column.

Correct Answer: The criteria range column headings must exactly match the column headings of the data being filtered

An Advanced Filter uses a separate criteria range whose header labels must match the field names in the list exactly. Criteria placed on the same row are combined with AND logic, while criteria on separate rows are combined with OR logic. Mismatched headers cause the filter to return incorrect results or nothing at all.

Correct Answer: They are combined with OR logic

In an Advanced Filter criteria range, criteria on the same row are joined with AND (all must be true), while criteria on different rows are joined with OR (any row's conditions can be met). Placing West in one row and East in the next under a Region header returns records matching either region.

Correct Answer: It automatically expands to include new adjacent rows and supports structured references and filter buttons

An Excel Table offers dynamic range expansion (new rows/columns are absorbed automatically), banded-row formatting, built-in filter and header buttons, a total row option, and structured references that use column names instead of cell addresses. These features make tables easier to maintain than plain ranges as data grows.

Correct Answer: =SUM(Sales[Revenue])

Structured references use the table name and column names enclosed in brackets rather than fixed cell addresses. =SUM(Sales[Revenue]) sums the entire Revenue column of the Sales table, and it automatically adjusts as rows are added or removed. This makes formulas more readable and self-updating than traditional A1-style range references.

Correct Answer: The value in the Price column on the current row (this row)

The @ operator in a structured reference means this row. A calculated column formula like =[@Quantity]*[@Price] multiplies the Quantity and Price values on the same row as the formula. Without the @, the reference would point to the whole column, which would produce a spilled or implicit intersection result.

Correct Answer: Sum

By default, Excel summarizes numeric fields placed in the Values area with the SUM function. Fields containing text or blanks default to Count instead. You can change the summary function by opening Value Field Settings and choosing Average, Count, Max, Min, Product, and others.