10 Spreadsheets Problem Set
SPREADSHEETS | PRACTICE PROBLEMS Complete the following to reinforce your understanding of the concept covered in this module.
PROBLEM 1: Given the data in the spreadsheet below, if the expressions shown in column D were evaluated, the results for cells D1:D5 respectively would be most close to:
A. −8, 36, 2, 3, 6 B. −2, 32, 1, 2, 6 C. −8, 36, 1, 2, 6 D. −2, 36, 2, 1, 4
Made with
by Prepineer | Prepineer.com
SOLUTION 1:
The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to calculate the VALUES that will be displayed for the cell range D1:D5 in Column D. It is important to realize that a colon (:) is used in the problem statement to indicate a range of cells needs to be evaluated, and not a single value.
As we looking to calculate the values that will be displayed after the expressions in cells D1:D5 are evaluated, we will work through each expression, and calculate the values for each expression.
Made with
by Prepineer | Prepineer.com
Starting with cell D1, we need to first identify the values that are being referenced into the expression. We do not need to worry about absolute or relative references as no cells are being copied or auto-filled.
Looking at the cells referenced into the expression for cell D1, we identify the values in the cell references are: A1=4, B2=4, and C3=3. Plugging these values into the expression for cell D1, we can re-write the expression as:
Made with
by Prepineer | Prepineer.com
Evaluating the re-written expression in cell D1, we calculate the displayed value for cell D1 as -8.
Moving on the expression in cell D2, we identify the values that are being referenced into the expression.
Made with
by Prepineer | Prepineer.com
Looking at the cells referenced into the expression for cell D2, we identify the values in the cell references are: A2=24, B2=4, and C2=2. Plugging these values into the expression for cell D2, we can re-write the expression as:
Evaluating the re-written expression in cell D2, we calculate the displayed value for cell D2 as 36.
Made with
by Prepineer | Prepineer.com
Moving on the expression in cell D3, we identify the values that are being referenced into the expression.
Looking at the cells referenced into the expression for cell D3, we identify the values in the cell references are: B4=2 and C1=1. Plugging these values into the expression for cell D3, we can re-write the expression as:
Made with
by Prepineer | Prepineer.com
Evaluating the re-written expression in cell D3, we calculate the displayed value for cell D3 as 2.
Moving on the expression in cell D4, we identify the values that are being referenced into the expression.
Made with
by Prepineer | Prepineer.com
Looking at the cells referenced into the expression for cell D4, we identify the values in the cell references are: A4=1, B2=4, and C1=1. Plugging these values into the expression for cell D4, we can re-write the expression as:
Evaluating the re-written expression in cell D4, we calculate the displayed value for cell D4 as 36.
Made with
by Prepineer | Prepineer.com
Moving on the final expression in cell D5, we identify the values that are being referenced into the expression.
Looking at the cells referenced into the expression for cell D4, we identify the values in the cell references are: A4=1 and C5=5. Plugging these values into the expression for cell D5, we can re-write the expression as:
Made with
by Prepineer | Prepineer.com
Evaluating the re-written expression in cell D5, we calculate the displayed value for cell D5 as 6.
We are now able to see all of the values that will be displayed in cells D1:D5 in column D.
Therefore, the correct answer choice is A. −𝟖, 𝟑𝟔, 𝟐, 𝟑, 𝟔
Made with
by Prepineer | Prepineer.com
PROBLEM 2: Given the data in the spreadsheet below, the result value displayed in cell E3 when the formula from cell A1 is copied into cell E3, is most close to:
A. 1 B. 2 C. 4 D. 5
Made with
by Prepineer | Prepineer.com
SOLUTION 2: The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to calculate the VALUE that will be calculated and displayed in cell E3 if the formula from cell A1 was copied to that cell. Looking at the expression in cell A1, we see that the expression calculates the sum of the values in the cell range $B1:$B2. We should note that both cell references are mixed, with the column references being absolute, and the row references being relative.
Made with
by Prepineer | Prepineer.com
Copying the formula from cell A1 to cell E3, there will be row translation, but no column translation. This is due to the column references of both cells being absolute.
Now that we have an expression for cell E3, we can identify the cells B3 and B4 that referenced in the expression.
Made with
by Prepineer | Prepineer.com
We identify the values in the referenced cells are B3=3 and B4=2, which we can then plug into the expression in cell E3.
Evaluating the values in the expression for cell E3, we calculate the displayed values to be 5.
Therefore, the correct answer choice is D. 5
Made with
by Prepineer | Prepineer.com
PROBLEM 3: Given the data in the spreadsheet below, if the formula from cell A3 was copied into cell D5, the formula in cell D5 would be most close to:
A. 𝐷2 ∗ 𝐷3 B. 𝐷3 ∗ 𝐷4 C. 𝐸2 ∗ 𝐸3 D. 𝐸3 ∗ 𝐸4
Made with
by Prepineer | Prepineer.com
SOLUTION 3:
The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to calculate the EXPRESSION for cell D5 if the formula from cell A3 is copied into it. Looking at the expression in cell A3, we see that the expression calculates the product of cells B1 and B2. Looking at cell references for cells B1 and B2, both are relative so there will be row and column translation for both references.
Made with
by Prepineer | Prepineer.com
Copying the formula from cell A3 to E5, there will be a translation of 2 rows downward and 3 columns to the right. Therefore, the row references will increase by 2 to reflect a translation of 2 rows downward, and the column references will increase by 3 letters to reflect a translation of 3 columns to the right.
Therefore, the correct answer choice is D. 𝑬𝟑 ∗ 𝑬𝟒
Made with
by Prepineer | Prepineer.com
PROBLEM 4: Given the data in the spreadsheet below, if the formula from cell A5 was copied into cell E4, the value displayed in cell E4 would be most close to:
A. 105 B. 100 C. 85 D. 999
Made with
by Prepineer | Prepineer.com
SOLUTION 4:
The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to calculate the VALUE that will be calculated and displayed in cell E4 if the formula from cell A5 was copied to that cell. Looking at the expression in cell A5, we see that the expression finds the maximum value in the cell range $B$1:$B5. We should note that the cell reference for $B$1 is absolute, and the cell reference for cell $B5 is mixed.
Made with
by Prepineer | Prepineer.com
Copying the formula from cell A5 to E4, the only relative reference is the row reference for cell B5. Therefore, the only translation that will occur is the cell reference $B5 changing to $B4, to reflect a translation of one row upwards.
Now that we have an expression for cell E4, we can identify the being referenced in the expression, which is the ranges of cell from $B$1 to $B4.
Made with
by Prepineer | Prepineer.com
Evaluating the values in the expression for cell E4, we find the maximum value in the cell range $B1$1:$B4 is 85.
Therefore, the correct answer choice is C. 𝟖𝟓
Made with
by Prepineer | Prepineer.com
PROBLEM 5: Which of the following statement is equivalent to the expression below? A1+A2+A3+B1+B2+B3
A. = 𝐴𝑑𝑑(𝐴1: 𝐵3) B. = 𝑆𝑢𝑚(𝐴1: 𝐵3) C. = 𝑆𝑢𝑚(𝐴1 + 𝐵3) D. 𝑁𝑜𝑛𝑒 𝑜𝑓 𝑡ℎ𝑒 𝑎𝑏𝑜𝑣𝑒
Made with
by Prepineer | Prepineer.com
SOLUTION 5:
The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to find an equivalent EXPRESSION for a group of cells being added together.
As the cells are being added together, we know that we need to find an equivalent function that will calculate the SUM of the identified cells. Therefore, we should use the SUM FUNCTION to indicate the identified cells should be added together.
Made with
by Prepineer | Prepineer.com
Now that we have identified that the SUM FUNCTION should be used, we need to identify the argument the function will evaluate. In the problem statement, we are told the values in cells A1, A2, A3 and B1, B2, B3 are being added together. Let’s go ahead and draw a table to help us see how those cells are organized on a spreadsheet.
A
B
1
A1
B1
2
A2
B2
3
A3
B3
We should realize that we can express the identified cells as a range, using a colon (:) to indicate the beginning point and end point of the range. As the range begins at cell A1 and ends at cell B3, we expression cell range as A1:B3.
Therefore, the correct answer choice is B. = 𝐒𝐔𝐌(𝐀𝟏: 𝐁𝟑)
Made with
by Prepineer | Prepineer.com
PROBLEM 1: Given the data in the spreadsheet below, if the expressions shown in column D were evaluated, the results for cells D1:D5 respectively would be most close to:
A. −8, 36, 2, 3, 6 B. −2, 32, 1, 2, 6 C. −8, 36, 1, 2, 6 D. −2, 36, 2, 1, 4
Made with
by Prepineer | Prepineer.com
SOLUTION 1:
The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to calculate the VALUES that will be displayed for the cell range D1:D5 in Column D. It is important to realize that a colon (:) is used in the problem statement to indicate a range of cells needs to be evaluated, and not a single value.
As we looking to calculate the values that will be displayed after the expressions in cells D1:D5 are evaluated, we will work through each expression, and calculate the values for each expression.
Made with
by Prepineer | Prepineer.com
Starting with cell D1, we need to first identify the values that are being referenced into the expression. We do not need to worry about absolute or relative references as no cells are being copied or auto-filled.
Looking at the cells referenced into the expression for cell D1, we identify the values in the cell references are: A1=4, B2=4, and C3=3. Plugging these values into the expression for cell D1, we can re-write the expression as:
Made with
by Prepineer | Prepineer.com
Evaluating the re-written expression in cell D1, we calculate the displayed value for cell D1 as -8.
Moving on the expression in cell D2, we identify the values that are being referenced into the expression.
Made with
by Prepineer | Prepineer.com
Looking at the cells referenced into the expression for cell D2, we identify the values in the cell references are: A2=24, B2=4, and C2=2. Plugging these values into the expression for cell D2, we can re-write the expression as:
Evaluating the re-written expression in cell D2, we calculate the displayed value for cell D2 as 36.
Made with
by Prepineer | Prepineer.com
Moving on the expression in cell D3, we identify the values that are being referenced into the expression.
Looking at the cells referenced into the expression for cell D3, we identify the values in the cell references are: B4=2 and C1=1. Plugging these values into the expression for cell D3, we can re-write the expression as:
Made with
by Prepineer | Prepineer.com
Evaluating the re-written expression in cell D3, we calculate the displayed value for cell D3 as 2.
Moving on the expression in cell D4, we identify the values that are being referenced into the expression.
Made with
by Prepineer | Prepineer.com
Looking at the cells referenced into the expression for cell D4, we identify the values in the cell references are: A4=1, B2=4, and C1=1. Plugging these values into the expression for cell D4, we can re-write the expression as:
Evaluating the re-written expression in cell D4, we calculate the displayed value for cell D4 as 36.
Made with
by Prepineer | Prepineer.com
Moving on the final expression in cell D5, we identify the values that are being referenced into the expression.
Looking at the cells referenced into the expression for cell D4, we identify the values in the cell references are: A4=1 and C5=5. Plugging these values into the expression for cell D5, we can re-write the expression as:
Made with
by Prepineer | Prepineer.com
Evaluating the re-written expression in cell D5, we calculate the displayed value for cell D5 as 6.
We are now able to see all of the values that will be displayed in cells D1:D5 in column D.
Therefore, the correct answer choice is A. −𝟖, 𝟑𝟔, 𝟐, 𝟑, 𝟔
Made with
by Prepineer | Prepineer.com
PROBLEM 2: Given the data in the spreadsheet below, the result value displayed in cell E3 when the formula from cell A1 is copied into cell E3, is most close to:
A. 1 B. 2 C. 4 D. 5
Made with
by Prepineer | Prepineer.com
SOLUTION 2: The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to calculate the VALUE that will be calculated and displayed in cell E3 if the formula from cell A1 was copied to that cell. Looking at the expression in cell A1, we see that the expression calculates the sum of the values in the cell range $B1:$B2. We should note that both cell references are mixed, with the column references being absolute, and the row references being relative.
Made with
by Prepineer | Prepineer.com
Copying the formula from cell A1 to cell E3, there will be row translation, but no column translation. This is due to the column references of both cells being absolute.
Now that we have an expression for cell E3, we can identify the cells B3 and B4 that referenced in the expression.
Made with
by Prepineer | Prepineer.com
We identify the values in the referenced cells are B3=3 and B4=2, which we can then plug into the expression in cell E3.
Evaluating the values in the expression for cell E3, we calculate the displayed values to be 5.
Therefore, the correct answer choice is D. 5
Made with
by Prepineer | Prepineer.com
PROBLEM 3: Given the data in the spreadsheet below, if the formula from cell A3 was copied into cell D5, the formula in cell D5 would be most close to:
A. 𝐷2 ∗ 𝐷3 B. 𝐷3 ∗ 𝐷4 C. 𝐸2 ∗ 𝐸3 D. 𝐸3 ∗ 𝐸4
Made with
by Prepineer | Prepineer.com
SOLUTION 3:
The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to calculate the EXPRESSION for cell D5 if the formula from cell A3 is copied into it. Looking at the expression in cell A3, we see that the expression calculates the product of cells B1 and B2. Looking at cell references for cells B1 and B2, both are relative so there will be row and column translation for both references.
Made with
by Prepineer | Prepineer.com
Copying the formula from cell A3 to E5, there will be a translation of 2 rows downward and 3 columns to the right. Therefore, the row references will increase by 2 to reflect a translation of 2 rows downward, and the column references will increase by 3 letters to reflect a translation of 3 columns to the right.
Therefore, the correct answer choice is D. 𝑬𝟑 ∗ 𝑬𝟒
Made with
by Prepineer | Prepineer.com
PROBLEM 4: Given the data in the spreadsheet below, if the formula from cell A5 was copied into cell E4, the value displayed in cell E4 would be most close to:
A. 105 B. 100 C. 85 D. 999
Made with
by Prepineer | Prepineer.com
SOLUTION 4:
The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to calculate the VALUE that will be calculated and displayed in cell E4 if the formula from cell A5 was copied to that cell. Looking at the expression in cell A5, we see that the expression finds the maximum value in the cell range $B$1:$B5. We should note that the cell reference for $B$1 is absolute, and the cell reference for cell $B5 is mixed.
Made with
by Prepineer | Prepineer.com
Copying the formula from cell A5 to E4, the only relative reference is the row reference for cell B5. Therefore, the only translation that will occur is the cell reference $B5 changing to $B4, to reflect a translation of one row upwards.
Now that we have an expression for cell E4, we can identify the being referenced in the expression, which is the ranges of cell from $B$1 to $B4.
Made with
by Prepineer | Prepineer.com
Evaluating the values in the expression for cell E4, we find the maximum value in the cell range $B1$1:$B4 is 85.
Therefore, the correct answer choice is C. 𝟖𝟓
Made with
by Prepineer | Prepineer.com
PROBLEM 5: Which of the following statement is equivalent to the expression below? A1+A2+A3+B1+B2+B3
A. = 𝐴𝑑𝑑(𝐴1: 𝐵3) B. = 𝑆𝑢𝑚(𝐴1: 𝐵3) C. = 𝑆𝑢𝑚(𝐴1 + 𝐵3) D. 𝑁𝑜𝑛𝑒 𝑜𝑓 𝑡ℎ𝑒 𝑎𝑏𝑜𝑣𝑒
Made with
by Prepineer | Prepineer.com
SOLUTION 5:
The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing. We must memorize this formula and understand its application independent of the NCEES Supplied Reference Handbook. The first step in this problem is identify the value(s) or expression(s) we need to solve for. Based on the problem statement, we are looking to find an equivalent EXPRESSION for a group of cells being added together.
As the cells are being added together, we know that we need to find an equivalent function that will calculate the SUM of the identified cells. Therefore, we should use the SUM FUNCTION to indicate the identified cells should be added together.
Made with
by Prepineer | Prepineer.com
Now that we have identified that the SUM FUNCTION should be used, we need to identify the argument the function will evaluate. In the problem statement, we are told the values in cells A1, A2, A3 and B1, B2, B3 are being added together. Let’s go ahead and draw a table to help us see how those cells are organized on a spreadsheet.
A
B
1
A1
B1
2
A2
B2
3
A3
B3
We should realize that we can express the identified cells as a range, using a colon (:) to indicate the beginning point and end point of the range. As the range begins at cell A1 and ends at cell B3, we expression cell range as A1:B3.
Therefore, the correct answer choice is B. = 𝐒𝐔𝐌(𝐀𝟏: 𝐁𝟑)
Made with
by Prepineer | Prepineer.com
