Spreadsheet - Functions and Charts

• Relative Cell Referencing (Default Mode):
1. Cell references in a formula are based on their relative position to the cell containing the formula.
2. When the formula is copied or dragged to another row or column, the cell references shift and adjust automatically.
Example: If cell $C1$ contains =A1 + B1, copying it down to $C2$ automatically changes to =A2 + B2.
• Absolute Cell Referencing:
1. The exact coordinates of a cell are permanently locked using the dollar sign **`$`** placed before both the column letter and row number.
2. When the formula is copied anywhere, the reference **remains strictly unchanged**.
*Example:* If cell $C1$ contains **`=$A$1 * B1`**, copying it to $C2$ becomes **`=$A$1 * B2`** ($A1$ stays locked).

• =COUNT(range):
Counts ONLY the cells that contain numerical numbers within the specified range. It completely ignores blank cells, text strings, and boolean values.
• =COUNTA(range):
Counts all NON-EMPTY cells containing any type of data—including numbers, text labels, dates, errors, or symbols (it ignores only completely blank cells).
• Example:
Suppose cells $A1:A5$ contain: 10, "Pass", 45, blank, "Fail".
• =COUNT(A1:A5) returns 2 (only 10 and 45 are numbers).
• =COUNTA(A1:A5) returns 4 (counts all 4 non-empty cells).

• The Formula:

$$\mathbf{=\text{IF}(B2 >= 40, \text{ "Pass"}, \text{ "Fail"})}$$

• Syntax Breakdown:
1. Logical Test: B2 >= 40
2. Value if TRUE: "Pass"
3. Value if FALSE: "Fail".

• (a) #####:
• Cause: The column is too narrow to display the complete numerical number, or a date is negative.
• Fix: Double-click the right boundary border of the column header to auto-widen the column.
• (b) #DIV/0!:
• Cause: A formula attempts to divide a number by zero (0) or by an empty blank cell.
• Fix: Change the divisor to a valid non-zero numerical value.
• (c) #NAME?:
• Cause: A function name is misspelled (e.g., typing =SUMM(A1:A5)) or text is entered without quotation marks.
• Fix: Correct the spelling of the function name to standard spreadsheet syntax (=SUM).

• Column Chart:
1. Uses vertical rectangular bars rising along the X-axis.
2. Used to compare multiple discrete categories and can display multiple data series simultaneously over time.
Example: Comparing quarterly sales across 4 different regional zones.
• Pie Chart:
1. Uses a circular disk divided into proportional wedge-shaped sectors representing parts of a whole ($100\%$).
2. Can display ONLY ONE single data series at a time.
Example: Displaying the percentage breakdown of a monthly family budget (Rent, Food, Savings).

• AutoFilter: A data management tool that allows users to selectively display only those rows that meet specified criteria, while temporarily hiding all other non-matching rows from view (e.g., displaying only students who scored $> 90\%$).
• Difference from Sorting:
• Sorting rearranges the physical order of all rows in the table alphabetically ($A\to Z$) or numerically (ascending/descending) without hiding any row.
• Filtering does not alter row order, but hides irrelevant data, leaving only targeted records visible.

• Analyze Mixed Referencing:
1. A$2`:** Column $A$ is relative; Row $2$ is **absolute (locked with `$).
• Moving from column $C$ to $D$ shifts column $A \to B$.
• Row $2$ stays locked at $2$. $\implies$ B$2`**.
2. **`$B3: Column $B$ is absolute (locked with $`)**; Row $3$ is relative.
• Column $B$ stays locked at $B$.
• Moving from row $3$ to row $5$ ($+2$ rows) shifts row $3 \to 5$. $\implies$ **`$B5.
• Resulting Formula in D5:
$$\mathbf{=B\$2 * \$B5}$$.

• Name Box: A small rectangular box located to the left of the formula bar that displays the cell address of the currently active selected cell (e.g., $B4$), or the name of a selected range.
• Formula Bar: A long horizontal strip located above the worksheet grid that displays the actual underlying formula or text content entered into the active cell, allowing users to create, view, and edit mathematical equations.