Formulas and Essential Functions

Every formula in Excel begins with an equals sign (=) — this tells Excel to treat what follows as a calculation rather than text or a number. Understanding the fundamentals of formula construction prevents the errors that trip up beginners:

• Basic arithmetic formulas use standard operators: + (addition), - (subtraction), * (multiplication), / (division), ^ (exponentiation). The formula =B2*C2 multiplies the value in B2 by the value in C2 — in a supply tracker, this calculates Total Cost = Quantity × Unit Cost. Excel follows standard mathematical order of operations (PEMDAS): parentheses first, then exponentiation, then multiplication and division left to right, then addition and subtraction left to right. Use parentheses liberally to make the order explicit: =(B2+C2)*D2 is clearer and safer than =B2+C2*D2.
• Cell references make formulas dynamic — instead of typing the actual numbers (=50*12.99), write the formula using cell addresses (=B2*C2). When the quantity or cost changes, the formula recalculates automatically. This is the fundamental reason formulas are more powerful than manual calculations: update the source data and every formula that references it updates instantly.
• Relative cell references adjust automatically when a formula is copied to another cell — if =B2*C2 is in cell E2 and you copy it down to E3, Excel changes it to =B3*C3 automatically. This is the behavior you want when copying a total cost formula down 20 rows — each row's formula refers to that row's own data. This is why you write the formula once in E2 and copy it down, rather than writing a new formula in every row.
• Absolute cell references use the $ sign to lock a reference so it does not change when copied — =$B$1 always refers to B1 regardless of where the formula is copied. Use absolute references when all rows should reference the same cell — such as a tax rate in B1 that should be applied to every row: =B2*$B$1. Press F4 while typing a cell reference to cycle through relative, absolute, and mixed reference styles.

Excel's built-in functions are pre-written formulas that perform common calculations — you provide the input (the range of cells to work with) and the function returns the result. These core functions cover the vast majority of calculations you will perform in a medical office setting:

• SUM adds all values in a range — =SUM(E2:E25) adds all values in cells E2 through E25. This is the most-used function in Excel. The AutoSum button (Σ) in the Home tab inserts =SUM() and automatically suggests the range based on adjacent data — click a cell below a column of numbers and press Alt+= to insert a SUM formula instantly.
• AVERAGE calculates the arithmetic mean of a range — =AVERAGE(C2:C25) returns the average quantity across all supply items. AVERAGE ignores blank cells (it does not count them in the denominator), which is the correct behavior for most data analysis. MIN and MAX return the smallest and largest values in a range: =MIN(C2:C25) finds the item with the lowest quantity, =MAX(D2:D25) finds the most expensive unit cost.
• COUNT counts the number of cells that contain numbers — =COUNT(C2:C25) counts how many cells in the Quantity range have numeric values. COUNTA counts all non-empty cells regardless of data type — =COUNTA(A2:A25) counts how many rows have an item name (useful for knowing how many items are in the tracker). COUNTBLANK counts empty cells — useful for finding missing data.
• IF evaluates a condition and returns one value if true and another if false — =IF(C2<=F2,"LOW","OK") checks whether the quantity in C2 is at or below the reorder level in F2 and returns 'LOW' if yes, 'OK' if no. This is exactly how a supply tracker Status column works. The syntax is =IF(logical_test, value_if_true, value_if_false). Nested IF allows chaining: =IF(C2=0,"OUT",IF(C2<=F2,"LOW","OK")) returns 'OUT' for zero quantity, 'LOW' for below reorder level, and 'OK' for adequate stock.
• ROUND controls the number of decimal places in a result — =ROUND(B2*C2, 2) multiplies B2 by C2 and rounds to 2 decimal places, preventing floating-point calculation errors from appearing in financial totals. TODAY() returns the current date as a serial number that updates every time the file is opened — useful for 'Date Last Updated' fields. NOW() returns the current date and time.

Formula errors are a normal part of working in Excel — the key is recognizing what each error code means so you can fix it quickly rather than panicking or deleting the formula:

• #DIV/0! means the formula is attempting to divide by zero — this happens when the divisor cell is empty or contains zero. For example, =AVERAGE(C2:C5) returns #DIV/0! if all four cells are empty. Fix by either ensuring the divisor is not empty, or wrapping the formula in IFERROR: =IFERROR(C2/D2, 0) returns 0 instead of the error if D2 is zero.
• #VALUE! means the formula references a cell that contains the wrong type of data — for example, =B2*C2 returns #VALUE! if C2 contains text instead of a number. Check the referenced cells to confirm they contain the expected data type. This is often caused by numbers accidentally stored as text.
• #REF! means the formula references a cell that no longer exists — this happens when rows or columns referenced by the formula have been deleted. Click the cell with #REF!, look in the formula bar for #REF! markers in the formula, and update the reference to point to the correct current cells.
• #NAME? means Excel does not recognize a name in the formula — usually caused by a typo in a function name (=SUMM() instead of =SUM()), a reference to a named range that does not exist, or missing quotation marks around text in a formula. Read the formula carefully and correct the spelling or syntax.
• Formula Auditing tools help you understand how a formula works — Formulas > Trace Precedents draws arrows to show which cells the selected formula uses as inputs. Formulas > Evaluate Formula walks through the calculation step by step, showing how Excel computes the result. These tools are invaluable when you inherit a spreadsheet built by someone else and need to understand what the formulas are doing.