Formulas & Functions

Formula Basics: Operators and Order of Operations

The true power of Excel lies not in storing data, but in calculating with it. A formula is an instruction that tells Excel to perform a calculation using values, cell references, and operators. Every formula in Excel begins with an equals sign (=). When Excel sees the equals sign, it knows that what follows is a calculation to evaluate, not just text or a number to display.

Arithmetic Operators

Excel uses standard arithmetic operators to perform calculations:

• + (Addition) – Adds values. Example: =B2+C2 adds the values in cells B2 and C2.
• - (Subtraction) – Subtracts one value from another. Example: =D2-E2 calculates the difference.
• * (Multiplication) – Multiplies values. Example: =F2*G2 multiplies the values in cells F2 and G2.
• / (Division) – Divides one value by another. Example: =H2/I2 divides the value in H2 by I2.
• ^ (Exponentiation) – Raises a number to a power. Example: =J2^2 squares the value in J2.

Order of Operations (PEMDAS)

When a formula contains multiple operators, Excel follows the mathematical order of operations, remembered by the acronym PEMDAS:

1. Parentheses – Operations inside parentheses are calculated first.
2. Exponents – Powers and roots are calculated next.
3. Multiplication and Division – Calculated left to right.
4. Addition and Subtraction – Calculated left to right.

This matters in healthcare calculations. Consider a formula to calculate a patient's Body Mass Index (BMI): BMI = weight (kg) / height (m) squared. In Excel, if weight is in cell B2 and height in meters is in cell C2, the formula would be =B2/C2^2. Excel first calculates C2^2 (height squared), then divides B2 by that result. If you wrote =B2/C2*C2 without understanding order of operations, Excel would divide B2 by C2 first, then multiply by C2, giving an incorrect result. To be explicit, you can use parentheses: =B2/(C2^2).

Building Your First Formula

To enter a formula:

1. Select the cell where you want the result to appear.
2. Type the equals sign (=).
3. Type the formula using cell references and operators (e.g., =B2+C2).
4. Press Enter to confirm. Excel displays the calculated result in the cell.

Instead of typing cell references, you can select cells while building a formula. After typing the equals sign, select cell B2 (Excel inserts B2 into the formula), type the operator (+), then select cell C2. This point-and-select method reduces errors, especially in large worksheets.

1. Functions — predefined formulas like PI() that return calculated values
2. References — cell addresses like A2 that pull values from other cells
3. Constants — numbers or text typed directly into the formula
4. Operators — symbols like + (add), * (multiply), and ^ (power)

Relative, Absolute, and Mixed Cell References

Understanding how Excel handles cell references when you copy formulas is one of the most important concepts in spreadsheet design. According to Microsoft's documentation, there are three types of cell references: relative, absolute, and mixed. Each behaves differently when a formula is copied from one cell to another.

Relative Absolute Mixed F4 Shortcut

Relative References (Default) – A reference like A1 tells Excel to look at a cell in a relative position from the formula cell. When you copy a formula containing a relative reference, the reference automatically adjusts based on the new location.

• Cell C2 contains the formula =A2+B2.
• When copied to C3, Excel adjusts it to =A3+B3.
• When copied to C4, it becomes =A4+B4.

This is exactly what you want in most situations. If column A contains patient systolic blood pressure and column B contains diastolic, a formula in column C that calculates the difference (=A2-B2) should adjust for each row as you copy it down.

Absolute References – Use dollar signs to lock a cell address so it does not change when the formula is copied. The syntax is $A$1. The dollar sign before the column letter locks the column; the dollar sign before the row number locks the row.

• Cell F1 contains a tax rate of 8.5% that applies to all items.
• Cell F2 contains the formula =E2*$F$1.
• When copied to F3, F4, F5, etc., E2 adjusts (E3, E4, E5) but $F$1 stays locked on cell F1.

Without the dollar signs, copying the formula would change F1 to F2, F3, and so on — pointing to empty cells and producing errors.

Mixed References – Lock either the row or the column, but not both:

• $A1 – The column (A) is locked; the row adjusts when copied.
• A$1 – The row (1) is locked; the column adjusts when copied.

Mixed references are used in more advanced formulas, such as lookup tables or matrices where you need one dimension to stay fixed while the other changes.

The F4 Shortcut – While editing a formula, place the cursor on a cell reference and press F4 to cycle through reference types:

1. First press: $A$1 (absolute)
2. Second press: A$1 (mixed — row locked)
3. Third press: $A1 (mixed — column locked)
4. Fourth press: A1 (relative, back to default)

This shortcut is far faster than manually typing dollar signs and is essential for building accurate formulas efficiently.

Essential Excel Functions: SUM, AVERAGE, COUNT, MIN, MAX

While formulas using arithmetic operators are powerful, Excel's built-in functions simplify common calculations dramatically. A function is a predefined formula that performs a specific calculation. Every function follows the same basic syntax:

=FUNCTIONNAME(argument1, argument2, ...)

The arguments inside the parentheses tell the function which cells or values to use. Let's examine the five most essential functions for healthcare data analysis.

The IF Function: Conditional Logic for Healthcare Decisions

The IF function is one of the most powerful and widely used functions in Excel because it enables your spreadsheet to make decisions. According to Microsoft's official documentation, the IF function checks whether a condition is true or false, then returns one value if TRUE and a different value if FALSE.

IF Function Syntax

The syntax of the IF function is:

=IF(logical_test, value_if_true, value_if_false)

• logical_test – The condition to evaluate. Typically a comparison using operators like >, <, =, >=, <=, or <>.
• value_if_true – The value returned if the condition is TRUE.
• value_if_false – The value returned if the condition is FALSE.

Step-by-Step: Building a Blood Pressure Alert

You have a patient vitals spreadsheet where column D contains systolic blood pressure readings. You want column E to display "HIGH" if the systolic BP is 140 or above and "Normal" if below 140.

1. Select cell E2 (next to the first patient's systolic reading in D2).
2. Type the equals sign: =
3. Type the function name: IF(
4. Enter the logical test: D2>=140 — checks whether systolic reading is 140 or higher.
5. Type a comma and enter the value if true: "HIGH" — text values must be enclosed in quotation marks.
6. Type another comma and enter the value if false: "Normal"
7. Close the parenthesis: )
8. Press Enter. Complete formula: =IF(D2>=140,"HIGH","Normal")

Sorting Data for Healthcare Insights

Once your spreadsheet contains data and formulas, you need tools to organize and explore that data. Sorting rearranges your data rows based on the values in one or more columns, making it easier to identify patterns, find specific records, and prepare data for reports.

Single-Column Sort

The simplest sort rearranges data based on one column:

1. Select any cell in the column you want to sort by.
2. Go to Home > Sort & Filter or Data > Sort.
3. Choose Sort A to Z (ascending) or Sort Z to A (descending).

For text, ascending means alphabetical order (A to Z); for numbers, ascending means smallest to largest; for dates, ascending means oldest to newest.

Multi-Column Sort

Multi-column sorting lets you sort by a primary column, then by a secondary column within ties. For example, sort patients first by department, then alphabetically by last name within each department.

1. Select any cell in your data range.
2. Go to Data > Sort to open the Sort dialog box.
3. Set the first sort level: Sort by Department, Order A to Z.
4. Select Add Level to add a second sort: Then by Last Name, Order A to Z.
5. Select OK.

Filtering Data and Data Validation

While sorting rearranges all your data, filtering temporarily hides rows that do not meet specific criteria, letting you focus on a subset. Excel's AutoFilter feature is the primary tool for filtering data.

Using AutoFilter

To enable AutoFilter:

1. Select any cell within your data range.
2. Go to Data > Filter (or Home > Sort & Filter > Filter).
3. Drop-down arrows appear in each header cell.

A filtered column's drop-down arrow changes to a funnel icon, reminding you that a filter is active. To clear a filter, select the funnel icon and choose Clear Filter From [Column Name]. Filtering hides rows but does not delete them — all hidden data remains in the worksheet.

Data Validation Basics

Data validation restricts the type of data that can be entered into specific cells, reducing errors at the point of entry. This is particularly important in healthcare, where accurate data entry can impact patient care and billing.

1. Select the cells where you want to restrict entry.
2. Go to Data > Data Validation.
3. Choose a validation type: Whole Number, Decimal, Date, Time, Text Length, or List.
4. Set the criteria (e.g., Whole Number between 50 and 250 for systolic BP readings).
5. Optionally, add an Input Message (a tooltip that guides the user) and an Error Alert (shown when invalid data is entered).

Named Ranges for Clarity

A named range assigns a descriptive name to a cell or range of cells, making formulas easier to read. Instead of =SUM(C2:C31), name the range C2:C31 as WaitTimes and write =SUM(WaitTimes). To create a named range: select the range, click in the Name Box (left of the formula bar), type a descriptive name, and press Enter.