2.2 Formulas, functions, and lookups

Calculate with the surgery workbook and check every result

A formula implements a rule. Before entering it, state its inputs, the required result, and one example you can check independently. Keep source records, parameters, calculations, and reported results distinguishable.

Continue in the surgery workbook, Patients_working.xlsx, with the Consultations Table from Excel basics and data tables. Write summary formulas on control, outside the source Table.

Why checked formulas matter

A copied formula can shift the wrong reference, a lookup can silently miss a patient, and a boundary rule can classify an exact threshold incorrectly. The result may still look reasonable. State the rule first, test a small example, and reconcile the final result with an independent calculation.

In this chapter, you will:

read formula syntax and control how references change when copied;
build calculated Table columns and summary functions;
express decision rules with IF, AND, and OR and test their boundaries;
calculate conditional counts, totals, and averages with explicit populations;
match patient records with exact XLOOKUP formulas and check the keys;
create dynamic-array and text results; and
trace, test, and reconcile the completed calculations.

2.2.1 Read the parts of a formula

Every formula starts with =. In =AVERAGE(Consultations[Registered duration]), AVERAGE is the function and Consultations[Registered duration] is its argument. The structured reference names the Table and field that supply the values. Excel evaluates the formula and displays its result in the cell.

Select a formula cell and compare the displayed result with the formula bar. Press F2 to edit the formula in place, then press Esc to leave it unchanged. Use the function tooltip to check the required argument order.

Official Microsoft tutorials

Microsoft’s Overview of formulas in Excel introduces formula parts, operators, constants, and functions. The Switch between relative, absolute, and mixed references tutorial shows how copied references change and how F4 changes the reference type. Use both tutorials before the copying practice below.

2.2.2 Separate inputs, calculations, and outputs

Keep entered or imported values separate from calculated values. A useful workbook distinguishes:

source fields, received from a person or system;
parameters, explicit values used by several formulas;
calculated fields, derived from source fields and parameters; and
reported measures, summaries presented to a decision maker.

Do not hide a business rule inside a long formula when it could be named and documented. For example, store a duration threshold in a labeled parameter cell and refer to that cell from the calculation.

Excel Formulas: Comma vs. Semicolon

Excel’s function tooltip shows the separator expected by your system while you type a formula.

In the examples below, we use a comma as the list separator. If you copy a formula into an installation that expects semicolons, replace the commas between function arguments with semicolons. Colons still denote cell ranges, such as A2:A6.

2.2.3 Understand references before copying

Compare the copied references in Table 1 before using a fixed parameter.

Table 1: Reference behavior when a formula is copied.

Reference	What happens when copied one row down and one column right
`G2`	Becomes `H3`; row and column are relative
`$G$2`	Remains `$G$2`; row and column are fixed
`[@Age]`	Refers to Age in the current Table row
`Consultations[Age]`	Refers to the Table’s whole `Age` column

Press F4 while a cell reference is selected in a formula to cycle through relative, absolute, and mixed references. The Excel reference records the 2 mixed forms used in older and 2-dimensional calculation models.

Use a fixed parameter reference when the same input applies to every row. For example, put Allowed excess (minutes) in control!A3 and 10 in B3. The formula =IF([@[Registered duration]]>[@[Expected duration]]+control!$B$3,"Review","Within allowance") uses that single allowance for each surgery.

Test expected duration 50 with actual durations 59, 60, and 61. With a 10-minute allowance, the results should be Within allowance, Within allowance, and Review. Equality belongs to the permitted side of this rule.

2.2.4 Copy and freeze references in a practice worksheet

Open reference_practice, the copy created in Excel basics and data tables. Use its original A to K columns for this experiment. The L2, L3, and M2 cells are practice cells and can be replaced as the instructions specify.

On baseline, label D2:D3 as Mean expected duration and Mean registered duration. Enter =AVERAGE(Consultations[Expected duration]) in E2 and =AVERAGE(Consultations[Registered duration]) in E3. Use Ctrl+1 to display one decimal place. These summary cells are separate from the cells used for the reference-copy experiment. The displayed means must be 49.6 and 65.9 minutes, respectively.

Copying a formula adjusts its relative references. Compare the copied formulas below, then fix the references that should remain unchanged:
- Open reference_practice, enter =SUM(A2:A2101) in L2, and confirm the result 2,206,050.
- Select cell L2, which now contains the sum of A2:A2101.
- Copy cell L2 (using CTRL+C), and paste it (CTRL+V) to cell M2. Look at the formula in cell M2. This is =SUM(B2:B2101). The formula in L2 was =SUM(A2:A2101). By copying this formula one column to the right (from column L to M), Excel has increased the column-reference to column A by one to B. In other words: in cell M2 the data of column B is added.
- Copy cell L2 (with CTRL+C), and paste it (CTRL+V) in cell L3. Look at the formula in cell L3. This is =SUM(A3:A2102). The formula in L2 was =SUM(A2:A2101). By copying the formula one row down, (from row 2 to 3), Excel has increased the row-references to both row 2 and row 2101 by one, to resp. 3 and 2102. So, in cell L3 we are no longer including cell A2 in the sum, and are now including the (empty) cell A2102! How do we prevent this? This is done by freezing the row references in cell L2 with a $-sign, as follows.

Change the contents of cell L2 to: =SUM(A$2:A$2101). When you now copy cell L2 to L3, the row references are “frozen’, i.e., are no longer changed (try this!).
- Similarly, column references are frozen by placing a $-sign before the column letter in a reference.
- You can quickly cycle through relative, absolute, and mixed references with F4. Select the reference A2:A2101 inside the formula, press F4 repeatedly, and observe each placement of the $ signs.
You can also press F4 immediately after having typed a cell reference in a formula. For example, type the following in cell L2: =SQRT(F2.
- Now press F4, and close the formula with a )-sign, and press Enter.
- Copy cell L2 to L3. The result is the same in both cells: =SQRT($F$2).

Return to consultation_history for the calculated columns below. To add a column without overwriting the helper list beside the Table, select a cell in the last Table column and use Insert > Table Columns to the Right from its right-click menu. Repeat for each new Table column.

2.2.5 Build calculations from small rules

Start with direct arithmetic and common summary functions:

SUM adds numeric values;
AVERAGE calculates the arithmetic mean;
COUNT counts numeric cells;
COUNTA counts nonblank cells;
COUNTBLANK counts blank cells;
MIN and MAX identify extremes; and
ROUND makes a rounding rule explicit.

Calculate the first Table columns

Add Duration difference to Consultations and enter =[@[Registered duration]]-[@[Expected duration]]. Check that the Table fills the formula down. A positive result means the surgery took longer than planned. For the record with ID 70 and the durations shown in Excel basics and data tables, the difference is 98.17 minutes.

Add Duration status using the allowance formula =IF([@[Registered duration]]>[@[Expected duration]]+control!$B$3,"Review","Within allowance"). Enter it in the first data row of that column and confirm 2,100 filled rows. IF(test, result_if_true, result_if_false) selects one result. Use AND when every condition must be true and OR when at least one condition must be true.

The patient workbook uses a boundary rule to classify age. Use the categories in Table 2 to check the values immediately before and at each boundary.

Table 2: Patient age groups and their boundaries.

Age in years	Patient age group
Below 18	Under 18
At least 18 and below 65	18 to 64
At least 65	65 and over

Enter Test age in control!G2, enter a test value in H2, and enter =IF(H2<18,"Under 18",IF(H2<65,"18 to 64","65 and over")) in I2. The formula classifies a valid numeric age. Check 17, 18, 64, and 65. Check the input type and decide how to handle a missing age before applying the rule.

You can express the same ordered boundaries with IFS. Enter =IFS(H2<18,"Under 18",H2<65,"18 to 64",TRUE,"65 and over") in J2. Excel tests each condition from left to right and returns the result for the first true condition. The final TRUE supplies the result for every remaining valid age. Repeat the 17, 18, 64, and 65 checks and confirm that columns I and J agree. Microsoft describes the argument order and supported versions in the IFS documentation.

Use the same four values to inspect AND and OR. Enter =AND(H2>=18,H2<65) in K2; only 18 and 64 return TRUE. Enter =OR(H2<18,H2>=65) in L2; only 17 and 65 return TRUE. These checks make the included side of both boundaries visible before the tests are placed inside another IF formula.

Compare registered and expected duration

Add a new column named Plan status to Consultations with Insert > Table Columns to the Right. Enter the rule below and confirm that it fills through the Table. This comparison has no allowance.

Use IF when a result depends on a stated condition. For example:

Enter Listing 1 in the first data cell of Plan status.

Listing 1: Compare registered duration with expected duration without an allowance.

=IF([@[Registered duration]]>[@[Expected duration]],"Over plan","Within plan")

The formula is useful only when the meanings of “actual,” “planned,” and the comparison rule are clear. Avoid embedding several unrelated decisions in one nested formula. Use helper columns when they make the logic easier to inspect.

When a rule uses a parameter, keep that value in a labeled cell and fix its reference when copying the formula. When a source value is legitimately missing, test the record status before performing arithmetic on it.

Classify durations at two hours

Keep the existing allowance-based Duration status column. Use Insert > Table Columns to the Right for the separate two-hour classification below.

Add Duration status (two hours). Enter =IF([@[Registered duration]]>120,"Longer than 2 hours","120 minutes or less"). The Table must fill the formula through all 2,100 rows.

2.2.6 Make conditional calculations with `IF`

The IF function is one of the most used functions. It returns one value if a condition is true, and another value if it is false. Use the following syntax: =IF(logical_test, value_if_true, value_if_false)

(If your Excel uses ; as list separator, replace the commas with semicolons.)

Create a worksheet named if_practice.

Fill the cells A1 to A10 with arbitrary numbers between 1 and 10. In B1 enter the formula: =IF($A1>=6, "pass", "fail")

In the first part of the formula, the condition is tested (here: whether the value of A1 is larger than or equal to 6). If the condition is met, the formula returns the text "pass", otherwise it returns "fail". Copy this formula to the cells B1 to B10.

If you want an empty cell as output, enter "" into the formula (note: these are two double quotes, not four single quotes!).

Use the patient fields when combining conditions. In if_practice, enter a surgery type in A2 and a registered duration in B2. In C2, enter =IF(AND(A2="WP174",B2>120),"Review WP174","Other record"). The duration must be above 120; a value of exactly 120 remains Other record.

In D2, enter =IF(OR(B2<30,B2>120),"Outside 30 to 120","From 30 to 120"). Test 29.99, 30, 120, and 120.01 to check both included boundaries.

An AND-function gives as output “TRUE” when all the conditions are met. When one or more conditions are not met, the output is “FALSE”.

An OR-function returns the logical value “TRUE” when one of the conditions is met and “FALSE” when none of the conditions is met. The AND-function and OR-function can also be used within an IF-function and combined with other functions.

Return to consultation_history. Use Insert > Table Columns to the Right to make space for the Notes column.

Flag records for people aged 65 and above for an extra inspection.

In the sheet consultation_history, add a new column called Notes and let Excel automatically add the text "extra inspection" for patients aged 65+ (and an empty cell otherwise). Use an IF formula and fill it down for all rows.

2.2.7 Summarize an explicit population

Use control!A6:A11 for the six purpose labels below and B6:B11 for their formulas. Keep the allowance parameter in B3. Format minutes to two decimal places, except the NI799 mean in B11, which should display one decimal.

Use Table 3 to choose a formula for the population and measure being reported.

Table 3: Summary formulas for the supplied surgery records.

Purpose	Example
Add all registered minutes	`=SUM(Consultations[Registered duration])`
Mean registered minutes	`=AVERAGE(Consultations[Registered duration])`
Count surgeries for women	`=COUNTIF(Consultations[Gender],"female")`
Count women older than 56	`=COUNTIFS(Consultations[Gender],"female",Consultations[Age],">56")`
Total minutes for women having `WP174`	`=SUMIFS(Consultations[Registered duration],Consultations[Gender],"female",Consultations[Surgery type],"WP174")`
Mean minutes for women having `NI799`	`=AVERAGEIFS(Consultations[Registered duration],Consultations[Gender],"female",Consultations[Surgery type],"NI799")`

The last two results should be 7,585.58 minutes and 57.2 minutes when the average is displayed to one decimal place. Verify the included rows with filters, then clear the filters. Ordinary SUM and AVERAGE formulas still include filtered-out rows; do not infer a formula’s population from what is currently visible.

COUNT counts numeric cells, COUNTA counts nonempty cells, and ROWS counts rows. They answer different questions. Excel’s AVERAGE ignores blanks and text but includes numeric zero. A missing duration therefore requires an explicit policy; replacing it with zero changes the reported average.

Conditional aggregation applies a measure to selected records. For the supplied patient workbook, use its actual field names:

Compare the criteria in Listing 2. All three formulas must include the same female WP174 records.

Listing 2: Count, total, and average for the same female WP174 population.

=COUNTIFS(Consultations[Gender],"female",Consultations[Surgery type],"WP174")
=SUMIFS(Consultations[Registered duration],Consultations[Gender],"female",Consultations[Surgery type],"WP174")
=AVERAGEIFS(Consultations[Registered duration],Consultations[Gender],"female",Consultations[Surgery type],"WP174")

The criteria define which rows are included. A reported average must state its denominator: all scheduled consultations, completed consultations, or patients with at least one consultation can produce different results.

2.2.8 Plan an exact-match lookup

An exact-match lookup retrieves a field using a shared value. Use the existing Patients Table to retrieve the recorded gender for a patient name. Enter Patient name in control!D2, Leslie Fields in E2, and =XLOOKUP(E2,Patients[Patient name],Patients[Gender],"Not found",0) in F2. The result is male. Change E2 to Not in data and confirm that the formula returns Not found. Restore Leslie Fields before continuing.

Before using a lookup, check whether the lookup field is unique and whether every required value exists. XLOOKUP returns the first match; it does not warn that several rows share the same name. Use =COUNTIF(Patients[Patient name],E2) to count the matches for the current name. The count for Leslie Fields is 1. The supplied Patients Table contains 3 other names that each occur twice. Use the unique Leslie Fields example to learn XLOOKUP. Organizational tables require a stable patient identifier.

When reading an existing VLOOKUP formula, its last argument FALSE requests an exact match. Do not omit that argument when exact matching is required.

Check the lookup field

Use XLOOKUP to retrieve a field from a table:

Use Listing 3 to retrieve the recorded gender for Leslie Fields, then check the lookup field for duplicates.

Listing 3: Exact lookup of a patient name in the Patients Table.

=XLOOKUP("Leslie Fields",Patients[Patient name],Patients[Gender],"Not found")

The supplied person table has no stable identifier. Three names occur twice, so a name is not a safe general-purpose key: XLOOKUP returns the first match. Before relying on a lookup field, test uniqueness with =COUNTIF(Patients[Patient name],"Leslie Fields"). Use this unique example to learn the function, but do not join organizational tables by name. A real join requires a stable ID.

The lookup value and lookup column must also use compatible data types. A numeric identifier and a text identifier that look identical may not match. The explicit "Not found" result exposes missing keys instead of silently returning an unrelated value.

Build the exact-match lookup

An older lookup example has member names in A2:A9 and membership numbers in D2:D9. To return Harry Carter’s membership number, the formula searches the name column and returns the value from the same row of the membership-number column.

Use XLOOKUP for the lookup constructed here. Type =XLOOKUP in an empty cell and press F1 to open Excel help for the function.

The function call in the example could be: =XLOOKUP("Harry Carter",$A$2:$A$9,$D$2:$D$9,"Not found")

Official Microsoft tutorials

Use Microsoft’s XLOOKUP function tutorial to identify the lookup value, lookup array, return array, and optional not-found result. The page includes an official video and worked examples. Microsoft’s Dynamic array formulas and spilled array behavior tutorial explains the spill range used later in this chapter.

Figure 1 shows the spilled list of surgery types and the lookup and filter results used in the recording.

Excel worksheet showing a spilled list of surgery types with XLOOKUP and FILTER results — Figure 1: Modern formula results beside the patient records used as their source.

Open reference_practice for the cell-address lookup below; its Patient ID and Registered duration fields remain in A and J. Use an empty practice cell outside A to K.

To find the surgery time of patient number 543 using XLOOKUP, type: =XLOOKUP(543,$A$2:$A$2101,$J$2:$J$2101) in reference_practice!P6. Label O6 Registered minutes for patient 543. The result must be 101.38.

Tip: When you copy formulas down, use $ signs to keep ranges fixed (e.g., $A$2:$A$2101).

Read legacy VLOOKUP formulas

Older organizational workbooks may contain =VLOOKUP(543,$A$2:$J$2101,10,FALSE). You must be able to explain that it performs an exact lookup in the first column and returns the tenth column. Do not use it to construct the new lookup above; XLOOKUP states the lookup and return ranges separately.

Give the data of the patient who is 50 years old with AX939, using the AutoFilter function.

On reference_practice, open the Age dropdown and choose Number Filters > Equals > 50. Open Surgery type, clear Select All, select AX939, and choose OK. The remaining record has Patient ID 1890. Clear both filters with Data > Clear before continuing; confirm all 2,100 records are visible.

2.2.9 Build dynamic arrays outside Tables

Use the spill-range explanation in Microsoft’s dynamic-array tutorial before entering the formulas.

A dynamic array formula can return several values. Excel places the formula in one cell and spills its results into neighboring cells. The source can be an Excel Table, while the spilled result must remain in the worksheet grid outside a Table. Microsoft documents the behavior and supported versions in Dynamic array formulas and spilled array behavior.

Create a worksheet named dynamic_arrays and complete these checks:

Enter =SORT(UNIQUE(Consultations[Surgery type])) in A2. Confirm that the result spills through A28, with AF241 first and ZH768 last.
Enter =ROWS(A2#) in B2. The # operator refers to the complete spill range that starts in A2. Confirm 27 surgery types.
Enter =FILTER(Consultations[Patient ID],(Consultations[Gender]="female")*(Consultations[Surgery type]="WP174"),"No records") in D2. Enter =ROWS(D2#) in E2 and confirm 123 records.
Change WP174 temporarily to NOT-A-CODE. Confirm the visible result No records, then undo the change.
Clear A2, type blocked in A3, and re-enter the SORT(UNIQUE(...)) formula in A2. Excel returns #SPILL! because A3 blocks the required result range. Delete A3 and confirm that the 27 values return.

2.2.10 Clean text and separate dates and times

TRIM removes extra ordinary spaces, CLEAN removes certain nonprinting characters, and LOWER or UPPER standardizes case. Preserve received text and write the cleaned result in a separate column or Power Query step. Use TEXTBEFORE and TEXTAFTER to separate text at a delimiter, TEXTSPLIT to return all parts, and & to combine text.

Excel stores a valid date-time as a date serial number plus a fractional day. For a numeric date-time in A2, =INT(A2) extracts the date and =MOD(A2,1) extracts the time. Apply Date and Time formats to the respective results. Formatting a text timestamp does not convert it into a valid date-time; import it with the correct type and locale first.

Create datetime_practice. In A2 enter =DATE(2026,9,28)+TIME(14,30,0), in B2 enter =INT(A2), and in C2 enter =MOD(A2,1). Use Ctrl+1 > Custom to display A2 as yyyy-mm-dd hh:mm, B2 as yyyy-mm-dd, and C2 as hh:mm. Confirm 2026-09-28 14:30, 2026-09-28, and 14:30. The formulas operate on the numeric date-time stored in A2.

In A5, enter Intake with the surrounding spaces. In B5 enter =TRIM(A5) and confirm Intake. Keep A5 unchanged so that the cleaned result can be compared with the received text.

The guided steps use these current functions:

TRIM removes extra ordinary spaces;
TEXTBEFORE returns the text before a delimiter;
TEXTAFTER returns the text after a delimiter;
TEXTSPLIT returns all separated parts as a dynamic array; and
&, CONCAT, or TEXTJOIN combines text.

Split and combine patient names

On patient_data, add Table columns named First name, Last name, and Surname, first name. Enter these formulas in the first data row:

=TEXTBEFORE([@[Patient name]]," ") in First name;
=TEXTAFTER([@[Patient name]]," ") in Last name; and
=[@[Last name]]&", "&[@[First name]] in Surname, first name.

Confirm that each formula fills through the Patients Table. For Leslie Fields, the results are Leslie, Fields, and Fields, Leslie. For Doyle Banks Junior, TEXTAFTER returns Banks Junior, so the remaining name is not discarded.

On dynamic_arrays, enter =TEXTSPLIT("Doyle Banks Junior"," ") in G2 and confirm that G2:I2 contains Doyle, Banks, and Junior. Microsoft lists the supported Excel versions and optional arguments in the TEXTBEFORE documentation.

Return to the Consultations Table for the combined identifier. Use Insert > Table Columns to the Right to make room without overwriting the helper list.

Add Patient-surgery code. Enter =[@[Patient ID]]&"-"&[@[Surgery type]].

2.2.11 Inspect, repair, and retest

Edit a formula in its cell

Use a formula cell on reference_practice for this editing exercise. Record its formula first, make a temporary edit, and restore the recorded formula afterwards.

When you made a mistake in a formula and you want to adjust that specific cell, click on the cell and press the F2 key. With the arrow keys, you can then navigate within the formula and correct the mistake.

Try this by going to a cell with a formula and selecting it with a mouse click, then press the F2 key. You will notice that this is a quick and easy way to adjust the cell.

Select Formulas > Show Formulas to inspect formulas, or Trace Precedents to see the cells used by a result. Compare the result with your expected value. Identify the first incorrect reference or operation, repair it, and repeat both the failed case and nearby cases.

Use the symptom in Table 4 to choose the first check for a failed formula.

Table 4: Formula symptoms and the first checks to perform.

Symptom	First check
`#N/A`	Is an exact lookup key missing?
`#VALUE!`	Does an input contain text where a number is required?
`#DIV/0!`	Is the denominator zero or absent?
`#REF!`	Was a referenced cell or column deleted?
Plausible but wrong result	Are references, criteria, units, and the included population correct?

Do not hide an unexplained error with IFERROR(...,0). Record its cause and apply a rule that fits the task. Use number formatting for display precision; use ROUND only when rounding is part of the calculation rule.

2.2.12 Diagnose common formula errors

Create a temporary worksheet named formula_errors and reproduce each error once so that you can recognize it. Use D2, D3, and D4 for the first three formulas in Table 5. For the final deletion example, first enter 7 in A1 and =A1 in B1. Delete column A by right-clicking its heading and choosing Delete; inspect the shifted formula cell for #REF!. The separate rows keep these experiments from overwriting each other:

Reproduce the errors in Table 5 on the temporary worksheet and explain each cause.

Table 5: Formula errors and the actions that produce them.

Formula or action	Visible result	Meaning
`=1/0`	`#DIV/0!`	division by zero
`=VALUE("forty")`	`#VALUE!`	text cannot be used as the requested number
`=XLOOKUP("Not in data",Patients[Patient name],Patients[Gender])`	`#N/A`	no matching lookup value
Enter `=A1` in `B1`, then delete column `A`	`#REF!`	a referenced cell or range was removed

Use Formulas > Evaluate Formula to step through one of the formulas. Fix the cause, then remove the temporary worksheet. Use IFERROR only when you can state why the error is expected; hiding every error also hides broken references and invalid data.

A formula review sentence

Explain each important formula as: For each..., calculate... using..., while keeping... fixed. If the sentence is unclear, revise the workbook or its documentation.

2.2.13 Trace formula inputs and outputs

The Formulas tab contains tools to build and audit formulas. Figure 2 shows Trace Precedents, Trace Dependents, Show Formulas, Error Checking, and Evaluate Formula in the current interface.

Current Excel Formulas tab showing Trace Precedents, Trace Dependents, Show Formulas, Error Checking, and Evaluate Formula — Figure 2: Current Formulas Ribbon with the formula-auditing commands visible.

Official Microsoft tutorials

Use Microsoft’s tutorials to display relationships between formulas and cells, evaluate a nested formula one step at a time, and show formulas. Then apply those commands to the patient calculations below.

Figure 3 shows a practice formula with color-coded references while it is being audited.

Excel formula under inspection with color-coded references to the expected value, actual value, and allowance — Figure 3: A formula under inspection with its cell references marked by color.

Select a cell that is used in one of the formulas you have just built. Press the Trace Dependents button in the Formulas-tab. You see that arrows appear to all cells using this cell. This allows you to determine whether you can safely erase a cell without changing things elsewhere in the worksheet. Press the Remove Arrows button to remove the arrows again.
Select a cell that contains a formula. Press the Trace Precedents button in the Formulas tab. “Precedent” means predecessor. You see that arrows appear to all cells used in the formula. Press the Remove Arrows button to remove the arrows again.

Check lookups and the same conditional population

Use unused labeled cells on control for these checks, keeping the allowance in B3.

On control, use =XLOOKUP("Leslie Fields",Patients[Patient name],Patients[Gender],"Not found"). First use COUNTIF to confirm that this name occurs once, then confirm that the lookup result is male. Temporarily replace the lookup value with "Not in data", confirm Not found, and undo the change.
Use COUNTIFS, SUMIFS, and AVERAGEIFS to calculate the count, total duration, and average duration for female records with surgery type WP174. Label every result and include the unit. Your checks must be 123 surgeries, 7,585.58 minutes, and 61.67 minutes.
Select one result formula and use Formulas > Trace Precedents. Select one source cell and use Trace Dependents. Then select Remove Arrows.
Reconcile one conditional result by filtering the source Table to the same gender and surgery type and reading the status-bar count and sum. Clear both filters with Data > Clear, confirm 2,100 records, and save.

2.2.14 Exercises

Keep all six completed columns in Consultations: Duration difference, Duration status, Plan status, Duration status (two hours), Notes, and Patient-surgery code. Confirm that each fills all 2,100 rows. Keep the six summary formulas on control and the allowance in control!B3. Clear all source filters before saving. The allowance, no-allowance, and two-hour classifications answer different questions and must remain separate. Record a normal case and a boundary case with expected and observed results. Make a deliberate reference error in a temporary copy, diagnose it, and retest after repair. Keep the checked working workbook.

2.2.M1 Predict a copied relative reference

Cell D2 contains =B2-C2. What formula appears after copying it to D5?

=B2-C2
=B5-C5
=$B$2-$C$2
=D2-D2

Solution

B. Both references are relative, so their row numbers move with the formula.

2.2.M2 Keep a threshold fixed

Column E compares each duration with the threshold stored in B1. Which reference keeps both the row and column fixed when the formula may be copied down or across?

B1
B$1
$B1
$B$1

Solution

D. Both the row and column are fixed, so the reference remains unchanged when the formula is copied in either direction.

2.2.M3 Select the conditional count

Which function is designed to count rows that satisfy more than one criterion?

COUNT
COUNTIF
COUNTIFS
SUMIFS

Solution

C. COUNTIFS accepts several criteria ranges and criteria. SUMIFS sums a numeric range after applying criteria.

2.2.M4 Expose a missing lookup key

Why should an XLOOKUP formula specify an explicit Not found result?

It sorts the lookup table automatically.
It exposes an unmatched identifier for investigation.
It converts text identifiers to numbers.
It removes duplicate records.

Solution

B. The message makes an unmatched key visible. It does not repair types or duplicates.

2.2.M5 Use AutoFill without skipping a gap

A calculated column contains formulas above and below one blank cell. Which action best protects the calculation?

Drag the fill handle across the entire Table column and inspect the result.
Double-click the fill handle and assume every row is filled.
Replace the Table with a plain range.
Enter the result as a fixed value in every row.

Solution

A. A visible fill followed by inspection handles the gap deliberately. Table calculated columns normally propagate formulas, but the result must still be checked.

2.2.Q1 Build and test spill and text formulas

On dynamic_arrays, create the sorted unique surgery-type list and the filtered list of female WP174 patient IDs. Record the 27 and 123 row checks. Reproduce and repair #SPILL! once. On patient_data, compare the positional name formulas with TEXTBEFORE and TEXTAFTER for Leslie Fields. Use TEXTSPLIT on Doyle Banks Junior and record its 3 returned cells.

2.2.M6 Conditional evaluation

In cell I2, there is a value “WP174” (Surgery type). If we apply the Excel formula =IF(I2="WP174", "Correct", "Incorrect") on Table 5, what will be the result?

WP174
Correct
Incorrect
Error

Average calculation

If you enter the formula =AVERAGE(G2:G6) in Excel considering the data in Table 5, what will it calculate?

The average of the values in the “Age” column
The average of the values in the “Expected duration” column
The average of the values in the “Registered duration” column
The average of the values in the “Order of surgery” column

Multiple criteria sum

Considering the data in Table 5, what is the formula =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50") calculating?

The total expected duration of surgeries for male patients above the age of 50
The sum of ages for male patients above 50
The total number of surgeries for male patients above 50
The total registered duration of surgeries for male patients above 50

SUMIFS Function

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

sum_range: The range to sum.
criteria_range1: The range to evaluate with criteria1.
criteria1: The criteria to apply to criteria_range1.
criteria_range2, criteria2, …: Additional ranges and their associated criteria.

Using constants in formulas

You are calculating the total cost of products based on their price and the number of units sold. The prices of the products are listed in column A, the number of units sold is listed in column B, and you want to multiply the price by the units sold in column C. You also want to apply a fixed discount of 10% from cell D2 to all the totals. Your task is to write a formula that you can drag down to calculate the total cost for each product after the discount is applied.

Use the worksheet values in Table 6 when choosing the formula.

Table 6: Data of products and units sold.

	`A`	`B`	`C`	`D`
1	`Price`	`Units Sold`	`Total Cost`	`Discount`
2	10.00	5		10%
3	15.00	3
4	12.00	4
5	8.00	6

You need to calculate the total cost for each row using the formula:

\[ \text{Total Cost} = (\text{Price} \times \text{Units Sold}) \times (1 - \text{Discount}) \]

Which formula would you use in C2 that you can drag down to calculate the total cost for each product, applying the fixed discount from D2?

=A2 * B2 * (1 - D2)
=A2 * B2 * (1 - $D$2)
=A$2 * B$2 * (1 - D$2)
=$A2 * $B2 * (1 - D$2)

Continue with importing and refreshing data.

2.2.1 Read the parts of a formula

2.2.2 Separate inputs, calculations, and outputs

2.2.3 Understand references before copying

2.2.4 Copy and freeze references in a practice worksheet

2.2.5 Build calculations from small rules

Calculate the first Table columns

Compare registered and expected duration

Classify durations at two hours

2.2.6 Make conditional calculations with IF

2.2.7 Summarize an explicit population

2.2.8 Plan an exact-match lookup

Check the lookup field

Build the exact-match lookup

2.2.9 Build dynamic arrays outside Tables

2.2.10 Clean text and separate dates and times

Split and combine patient names

2.2.11 Inspect, repair, and retest

Edit a formula in its cell

2.2.12 Diagnose common formula errors

2.2.13 Trace formula inputs and outputs

Check lookups and the same conditional population

2.2.14 Exercises

2.2.M1 Predict a copied relative reference

2.2.M2 Keep a threshold fixed

2.2.M3 Select the conditional count

2.2.M4 Expose a missing lookup key

2.2.M5 Use AutoFill without skipping a gap

2.2.Q1 Build and test spill and text formulas

2.2.M6 Conditional evaluation

Average calculation

Multiple criteria sum

Using constants in formulas

2.2.6 Make conditional calculations with `IF`