09 Spreadsheets Concept Overview
SPREADSHEETS | CONCEPT OVERVIEW The topic of SPREADSHEETS is not provided in the NCEES Supplied Reference Handbook, Version 9.4 for Computer Based Testing.
CONCEPT INTRO: A SPREADSHEET is an electronic document where data is arranged in ROWS and COLUMNS of a grid, using elements referred to as CELLS. Front-end APPLICATIONS provide an interface for users to analyze and manipulate data using BUILT-IN FUNCTIONS and FEATURES. A WORKBOOK is the STANDARD FILE TYPE used in spreadsheet application. Each workbook has at least one WORKSHEET which provides the grid of rows and columns that a user works within.
Made with
by Prepineer | Prepineer.com
A COLUMN is a VERTICAL GROUPING of cells that increases in ALPHABETICAL ORDER from left to right. At the top of each worksheet are the letters, A, B, C, etc. which represent the columns of the worksheet.
A ROW is a HORIZONTAL GROUPING of cells that increases in NUMERICAL ORDER.
from top to bottom. On the far left column of each worksheet are the
numbers (1, 2, 3, etc.) that represent the rows of the worksheet.
Made with
by Prepineer | Prepineer.com
A CELL is a unique element identified by a CELL REFERENCE consisting of column letter and the row number. A cell address represents the intersection of the column and row that the cell is located. For example, the cell reference A1 represents the cell at the intersection of the first column (A) and the first row (1).
In the figure above, we can describe the highlighted cell as: • A1 represents the cell at the intersection of the first column (A) and the first row (1) formatted for currency • B3 represents the cell at the intersection of the second column (b) and the third row (3) given as a scientific number with 2 decimal places • D2 represents the cell at the intersection of the fourth column (D) and the second row (2) formatted as a percentage. A CELL RANGE is a rectangular region of cell on the spreadsheet that may consist of a single cell, a row, a column, or a region made up of two or more columns or rows. A cell range is identified by the address of the cell at the upper left corner of the rectangle and the address of the cell at the lower right corner of the range. For example: B2:D3 would identify a six-cell range that includes the cells with the address B2, C2, D,2, B3, C3, and D3.
Made with
by Prepineer | Prepineer.com
FORMULAS: A FORMULA is an expression that drives a calculation and results in a unique value within a cell. Formulas may include MATHEMATICAL OPERATORS (+,-,*,/), REFERENCES to other cells, MATHEMATICAL FUNCTIONS, or other built-in spreadsheet functions. A formula is initiated in a cell when it is prepended with an EQUALS SIGN (=) indicating that the succeeding expression represents a formula. Cells may contain manually entered data or the resultant of calculations pulled from other cells. When dealing with formulas, data in cells are DYNAMICALLY UPDATED when the reference data is changed. Below are some examples how to perform common arithmetic operations by creating an expression and using the respective arithmetic operator to indicate the operation to be performed:
Made with
by Prepineer | Prepineer.com
Formulas are evaluated according to the ORDER OF OPERATIONS presented for each ARITHMETIC OPERATOR in the formula. A common ACRONYM used to remember the order of operations is the abbreviation “PEMDAS”, which represents the phrase “Please Excuse My Dear Aunt Sally”. This phrase states the arithmetic operations should be performed in the following order represented by the first letter of each operator: • Parentheses • Exponents (including powers and square roots) • Multiplication • Division • Addition • Subtraction Operations that are equal in precedence are performed left to right. The order of calculation is controlled by using PARANTHESES to group operations that should be performed first. SPREADSHEET APPLICATIONS contain a library of BUILT-IN FUNCTIONS or predefined formulas that perform TEMPLATE CALCULATIONS or operations over a specified range of data.
Made with
by Prepineer | Prepineer.com
A FUNCTION is comprised of an equals sign (=), a function name, and the arguments of the function. The ARGUMENTS of the function are the values or cell references that contain the information the function is evaluating.
Individual values or cell references with the functions parentheses must be separated by either COLONS (:) or COMMAS (,). Colons are used to indicate a RANGE OF CELLS, while commas are used to separate individual values, cell references, or cell ranges.
Made with
by Prepineer | Prepineer.com
TYPES OF CELL REFERENCES: Using a spreadsheet, one has the ability to write formulas that reference cells throughout the workbook. It is important to note that when COPYING or AUTOFILLING FORMULAS, the values in certain cells may change or remain the same depending on how the cells are referenced into the expression. AUTOFILL is a feature in Microsoft Excel that allows the user to drag the contents of one cell to other cells or ranges of cells. Using the autofill features, the user can copy expressions and quickly fill cells with the UPDATED REFERENCES of a formula or identify a SEQUENCE the data is following such as days of the week, months of the year, numerical order, etc. When the expression of one cell is copied to another cell, the results are NOT COPIED, but rather the formula is MODIFIED by both the COLUMN TRANSLATION and ROW TRANSLATION between the source cell and destination cells of the expression. There are three (3) types of cell references used in expressions that define whether the cells referenced in formulas and functions should move relative to the translation of the columns and rows, or stay defined to a particular cell or range of cells. A RELATIVE CELL REFERENCE is a cell reference that updates the values for the row and column based on the amount of ROW TRANSLATION and COLUMN TRANSLATION between the source cells and destination cells of the expression.
Made with
by Prepineer | Prepineer.com
The standard default for referencing cells in Excel is a RELATIVE REFERENCE that automatically changes cell references based on the direction of translation. When copied across multiple cells, they change based on the relative position of the rows and columns. As shown in the table below, if you copy the formula =A1+B1 from cell A1 to cell A2, the formula will become =A2+B2. Relative references are used when formulas need to be repeated across multiple rows and columns.
Copying to the RIGHT INCREASES the column letter of a cell, and copying the to the LEFT DECREASES the column letter of a cell. This is true unless a “$” symbol is in front of the letter, to indicate the column is absolute. Copying DOWN INCREASES the row number of a cell, and copying UP DECREASES the row number of a cell. This is true unless a “$” symbol is in front of the number, to indicate the row is absolute.
Made with
by Prepineer | Prepineer.com
An ABSOLUTE CELL REFERENCE is a cell reference that DOES NOT CHANGE when the expression of the source cell is copied or translated elsewhere on the spreadsheet. An absolute reference uses the symbol “$” before a column letter AND row number to indicate the values will remain CONSTANT regardless of where the expression is referenced or copied.
A MIXED CELL REFERENCE is a cell reference that is a combination of absolute and relative references for either the row or column value. A mixed cell reference will update a cell with respect to either row translation or column translation, but NOT BOTH.
Made with
by Prepineer | Prepineer.com
CONCEPT EXAMPLE: Given the data in the illustrated spreadsheet grid below, the value displayed in cell B3 when the formula from B1 is copied into cell B3 is most close to:
A. 1 B. 2 C. 4 D. 6
Made with
by Prepineer | Prepineer.com
SOLUTION: 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 B3 if the formula from cell B1 was copied down.
Looking at the formula in cell B1, we see that the cell references for cells A1 and C2 are mixed. For the A1 cell references, the column reference is absolute, and the row reference is relative. For the C2 cell references, the column reference is relative, and the row reference is absolute.
Made with
by Prepineer | Prepineer.com
Therefore, as the formula from cell B1 is dragged down to cell B3, the only reference that will change is the row reference for cell A1. As we are translating two rows, the row reference in the expression for cell A1 will change from $A1 to $A3 to indicate a translation of two rows.
Now that we have an expression for the formula in cell B3, we can evaluate the expression by plugging in the values for the referenced cells. In cell A3, there is a value of 6, and in cell C2 there is a value of -4.
Made with
by Prepineer | Prepineer.com
Plugging in the values of 6 for cell A3 and -4 for cell C2, we can re-write the formula in cell B3 as:
Evaluating the expression in cell B3, we find the value displayed in cell B3 is 2.
Therefore, the correct answer choice is B. 𝟐
Made with
by Prepineer | Prepineer.com
CONCEPT INTRO: A SPREADSHEET is an electronic document where data is arranged in ROWS and COLUMNS of a grid, using elements referred to as CELLS. Front-end APPLICATIONS provide an interface for users to analyze and manipulate data using BUILT-IN FUNCTIONS and FEATURES. A WORKBOOK is the STANDARD FILE TYPE used in spreadsheet application. Each workbook has at least one WORKSHEET which provides the grid of rows and columns that a user works within.
Made with
by Prepineer | Prepineer.com
A COLUMN is a VERTICAL GROUPING of cells that increases in ALPHABETICAL ORDER from left to right. At the top of each worksheet are the letters, A, B, C, etc. which represent the columns of the worksheet.
A ROW is a HORIZONTAL GROUPING of cells that increases in NUMERICAL ORDER.
from top to bottom. On the far left column of each worksheet are the
numbers (1, 2, 3, etc.) that represent the rows of the worksheet.
Made with
by Prepineer | Prepineer.com
A CELL is a unique element identified by a CELL REFERENCE consisting of column letter and the row number. A cell address represents the intersection of the column and row that the cell is located. For example, the cell reference A1 represents the cell at the intersection of the first column (A) and the first row (1).
In the figure above, we can describe the highlighted cell as: • A1 represents the cell at the intersection of the first column (A) and the first row (1) formatted for currency • B3 represents the cell at the intersection of the second column (b) and the third row (3) given as a scientific number with 2 decimal places • D2 represents the cell at the intersection of the fourth column (D) and the second row (2) formatted as a percentage. A CELL RANGE is a rectangular region of cell on the spreadsheet that may consist of a single cell, a row, a column, or a region made up of two or more columns or rows. A cell range is identified by the address of the cell at the upper left corner of the rectangle and the address of the cell at the lower right corner of the range. For example: B2:D3 would identify a six-cell range that includes the cells with the address B2, C2, D,2, B3, C3, and D3.
Made with
by Prepineer | Prepineer.com
FORMULAS: A FORMULA is an expression that drives a calculation and results in a unique value within a cell. Formulas may include MATHEMATICAL OPERATORS (+,-,*,/), REFERENCES to other cells, MATHEMATICAL FUNCTIONS, or other built-in spreadsheet functions. A formula is initiated in a cell when it is prepended with an EQUALS SIGN (=) indicating that the succeeding expression represents a formula. Cells may contain manually entered data or the resultant of calculations pulled from other cells. When dealing with formulas, data in cells are DYNAMICALLY UPDATED when the reference data is changed. Below are some examples how to perform common arithmetic operations by creating an expression and using the respective arithmetic operator to indicate the operation to be performed:
Made with
by Prepineer | Prepineer.com
Formulas are evaluated according to the ORDER OF OPERATIONS presented for each ARITHMETIC OPERATOR in the formula. A common ACRONYM used to remember the order of operations is the abbreviation “PEMDAS”, which represents the phrase “Please Excuse My Dear Aunt Sally”. This phrase states the arithmetic operations should be performed in the following order represented by the first letter of each operator: • Parentheses • Exponents (including powers and square roots) • Multiplication • Division • Addition • Subtraction Operations that are equal in precedence are performed left to right. The order of calculation is controlled by using PARANTHESES to group operations that should be performed first. SPREADSHEET APPLICATIONS contain a library of BUILT-IN FUNCTIONS or predefined formulas that perform TEMPLATE CALCULATIONS or operations over a specified range of data.
Made with
by Prepineer | Prepineer.com
A FUNCTION is comprised of an equals sign (=), a function name, and the arguments of the function. The ARGUMENTS of the function are the values or cell references that contain the information the function is evaluating.
Individual values or cell references with the functions parentheses must be separated by either COLONS (:) or COMMAS (,). Colons are used to indicate a RANGE OF CELLS, while commas are used to separate individual values, cell references, or cell ranges.
Made with
by Prepineer | Prepineer.com
TYPES OF CELL REFERENCES: Using a spreadsheet, one has the ability to write formulas that reference cells throughout the workbook. It is important to note that when COPYING or AUTOFILLING FORMULAS, the values in certain cells may change or remain the same depending on how the cells are referenced into the expression. AUTOFILL is a feature in Microsoft Excel that allows the user to drag the contents of one cell to other cells or ranges of cells. Using the autofill features, the user can copy expressions and quickly fill cells with the UPDATED REFERENCES of a formula or identify a SEQUENCE the data is following such as days of the week, months of the year, numerical order, etc. When the expression of one cell is copied to another cell, the results are NOT COPIED, but rather the formula is MODIFIED by both the COLUMN TRANSLATION and ROW TRANSLATION between the source cell and destination cells of the expression. There are three (3) types of cell references used in expressions that define whether the cells referenced in formulas and functions should move relative to the translation of the columns and rows, or stay defined to a particular cell or range of cells. A RELATIVE CELL REFERENCE is a cell reference that updates the values for the row and column based on the amount of ROW TRANSLATION and COLUMN TRANSLATION between the source cells and destination cells of the expression.
Made with
by Prepineer | Prepineer.com
The standard default for referencing cells in Excel is a RELATIVE REFERENCE that automatically changes cell references based on the direction of translation. When copied across multiple cells, they change based on the relative position of the rows and columns. As shown in the table below, if you copy the formula =A1+B1 from cell A1 to cell A2, the formula will become =A2+B2. Relative references are used when formulas need to be repeated across multiple rows and columns.
Copying to the RIGHT INCREASES the column letter of a cell, and copying the to the LEFT DECREASES the column letter of a cell. This is true unless a “$” symbol is in front of the letter, to indicate the column is absolute. Copying DOWN INCREASES the row number of a cell, and copying UP DECREASES the row number of a cell. This is true unless a “$” symbol is in front of the number, to indicate the row is absolute.
Made with
by Prepineer | Prepineer.com
An ABSOLUTE CELL REFERENCE is a cell reference that DOES NOT CHANGE when the expression of the source cell is copied or translated elsewhere on the spreadsheet. An absolute reference uses the symbol “$” before a column letter AND row number to indicate the values will remain CONSTANT regardless of where the expression is referenced or copied.
A MIXED CELL REFERENCE is a cell reference that is a combination of absolute and relative references for either the row or column value. A mixed cell reference will update a cell with respect to either row translation or column translation, but NOT BOTH.
Made with
by Prepineer | Prepineer.com
CONCEPT EXAMPLE: Given the data in the illustrated spreadsheet grid below, the value displayed in cell B3 when the formula from B1 is copied into cell B3 is most close to:
A. 1 B. 2 C. 4 D. 6
Made with
by Prepineer | Prepineer.com
SOLUTION: 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 B3 if the formula from cell B1 was copied down.
Looking at the formula in cell B1, we see that the cell references for cells A1 and C2 are mixed. For the A1 cell references, the column reference is absolute, and the row reference is relative. For the C2 cell references, the column reference is relative, and the row reference is absolute.
Made with
by Prepineer | Prepineer.com
Therefore, as the formula from cell B1 is dragged down to cell B3, the only reference that will change is the row reference for cell A1. As we are translating two rows, the row reference in the expression for cell A1 will change from $A1 to $A3 to indicate a translation of two rows.
Now that we have an expression for the formula in cell B3, we can evaluate the expression by plugging in the values for the referenced cells. In cell A3, there is a value of 6, and in cell C2 there is a value of -4.
Made with
by Prepineer | Prepineer.com
Plugging in the values of 6 for cell A3 and -4 for cell C2, we can re-write the formula in cell B3 as:
Evaluating the expression in cell B3, we find the value displayed in cell B3 is 2.
Therefore, the correct answer choice is B. 𝟐
Made with
by Prepineer | Prepineer.com
