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

Correct Answer: To summarize and describe the main features of a dataset

Descriptive statistics provide a way to present data in a meaningful way, summarizing key characteristics without inferring conclusions about a population.

Correct Answer: do?

Correct Answer: 28

Excel follows the order of operations: exponents first, then multiplication/division, then addition/subtraction. Here 3^2 evaluates to 9, then 2*9 gives 18, and finally 10+18 equals 28. Working strictly left to right without precedence would give a wrong result, which is why the exponent and multiplication are resolved before the addition.

Correct Answer: #DIV/0!

The #DIV/0! error is returned whenever a formula attempts to divide by zero or by a cell that is blank (treated as zero). For example, =A1/A2 where A2 is empty produces #DIV/0!. Using IFERROR or checking the divisor with an IF statement is the common way to suppress this error.

Correct Answer: =A2*$B$1

$B$1 is an absolute reference, so the dollar signs lock both the column and row, keeping it as $B$1 when copied. A1 is a relative reference, so copying the formula one row down changes it to A2. The result is =A2*$B$1, a classic use of absolute referencing to multiply a column of values by a fixed constant.

Correct Answer: The value from the third column of the row where Smith is found in the first column, requiring an exact match

VLOOKUP searches for the lookup value in the leftmost column of the table array, then returns a value from the column indicated by the column index number (3 here). The FALSE fourth argument forces an exact match, so if Smith is not found in column A the function returns #N/A. The value returned comes from column C of the matching row.

Correct Answer: INDEX/MATCH

INDEX/MATCH is a flexible lookup combination where MATCH finds the position of a value and INDEX returns the value at that position. Unlike VLOOKUP, which can only look to the right of its lookup column, INDEX/MATCH can return a value from any column relative to the matched row, making it more versatile for lookups where the result column sits to the left.

Correct Answer: Pass

The >= operator means greater than or equal to, so the logical test A1>=60 is TRUE when A1 equals exactly 60. Because the condition is TRUE, the IF function returns its second argument, Pass. Only values strictly below 60 would trigger the Fail result.

Correct Answer: Excel does not recognize text in the formula, such as a misspelled function name

The #NAME? error appears when Excel cannot recognize text within a formula, typically due to a misspelled function name (like =AVERGE instead of =AVERAGE), an undefined named range, or missing quotation marks around text strings. Correcting the spelling or defining the referenced name resolves the error.

Correct Answer: The sum of values in B2:B10 where the corresponding cell in A2:A10 equals East

SUMIF adds the values in the sum_range (B2:B10) only for rows where the corresponding cell in the criteria range (A2:A10) meets the condition East. This lets you total a numeric column based on a matching label in another column. The count of matches would require COUNTIF instead.

Correct Answer: Only the row 5 is locked

=B$5 is a mixed reference. The dollar sign appears only before the row number, so the row (5) stays fixed when the formula is copied, while the column letter (B) is free to change. Mixed references are useful when you want to lock one dimension but allow the other to adjust during copying.

Correct Answer: =CONCATENATE(A1," ",B1)

CONCATENATE joins multiple text strings into one. To insert a space between the joined values, you include a literal space in quotes as a separate argument: =CONCATENATE(A1," ",B1). The ampersand operator achieves the same result. Using + on text would produce a #VALUE! error rather than joining the strings.

Correct Answer: 7

The MEDIAN is the middle value of a sorted dataset. With five values (3, 5, 7, 9, 11), the middle position is the third value, which is 7. Because there is an odd number of values, the median is simply that central number and is unaffected by how extreme the smallest or largest values are.

Correct Answer: A formula references cells that have been deleted

The #REF! error signals an invalid cell reference, most often because the referenced cells were deleted, or a formula was copied to a location where its relative references fall outside the worksheet. Restoring the deleted cells or adjusting the formula to point to valid references corrects it.

Correct Answer: MODE

The MODE function identifies the value that appears most often in a dataset. For example, =MODE(A1:A10) returns the number that repeats most frequently in that range. If no value repeats, MODE returns the #N/A error. It differs from MEDIAN (the middle value) and AVERAGE (the arithmetic mean).

Correct Answer: The average of all cells in the range D2:D50

A named range is a label assigned to a specific cell range, and it can be used in formulas in place of the cell addresses. Since Sales refers to D2:D50, =AVERAGE(Sales) is equivalent to =AVERAGE(D2:D50) and returns the arithmetic mean of those cells. Named ranges improve formula readability and reduce reference errors.

Correct Answer: Searches for Q3 in the first row and returns the value from the fourth row of the matching column

HLOOKUP performs a horizontal lookup, searching for the lookup value across the top row of the table array. Once it finds Q3 in the first row, it returns the value from the row indicated by the row index number (4). The FALSE argument requires an exact match, returning #N/A if Q3 is not found.

Correct Answer: =COUNT(A1:A20)

The COUNT function counts only cells that contain numbers, ignoring text, blanks, and logical values. COUNTA, by contrast, counts all non-empty cells regardless of content type. So =COUNT(A1:A20) returns the tally of numeric values only, which is the desired result here.

Correct Answer: Medium

This is a nested IF. The first test A1>100 is FALSE for 75, so evaluation moves to the second IF. There, A1>50 is TRUE (75 is greater than 50), so the function returns Medium. Nested IFs are evaluated from the outside in, stopping at the first condition that is TRUE.

Correct Answer: Using text in a formula that expects a number

The #VALUE! error occurs when a formula includes the wrong type of argument, such as trying to perform arithmetic on text. For example, =A1+A2 where A1 contains the word apple produces #VALUE!. Ensuring all operands in a mathematical formula are numeric resolves the error.