Excel formulas on patient data

Exercise 1: Patient workbook

The questions below refer to the data in Patients.xlsx.

Inspect the workbook and its data

The workbook contains two worksheets:

consultation_history: Patient consultation history (see Table 1).
patient_data: Name and gender of patients (see Table 2).

Table 1: Patient consultation history (Sheet consultation_history).

	A	B	C	D	E	F	G	H	I	J	K
1	Patient ID	Week	Day	OR	Gender	Age	Expected duration	Order of surgery	Surgery type	Registered duration	Surgery types
2	70	0	1	1	male	83	62.44	13	WP174	160.61	AF241
3	1215	5	3	2	male	48	58.0	10	ID810	158.21	AX939
4	1439	6	4	0	male	61	58.0	10	ID810	151.68	BP285
5	502	2	1	2	male	43	58.0	11	ID810	142.66	CP913
6	1120	5	1	1	male	65	62.44	13	WP174	142.47	GV023

Table 2: Patient names and genders (Sheet patient_data).

	A	B
1	Patient name	Gender
2	Leslie Fields	male
3	Arnold Ross	male
4	Craig Vega	male
5	Doyle Banks Junior	male
6	Herbert Freeman	male

Formula separators

In some countries, the comma (,) is used as a decimal separator, and the semicolon (;) is used as a list separator. If you are using Excel in a country where the comma is used as a decimal separator, you may need to use the semicolon as a list separator in Excel formulas.

By default, Excel for Windows uses the list separator in Windows regional settings. Excel’s function tooltip shows the separator expected by your system while you type a formula. The Use system separators option under File > Options > Advanced controls decimal and thousands separators, not the formula list separator.

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.

M1.1: 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 1, what will be the result?

WP174
Correct
Incorrect
Error

M1.2: Vertical lookup

If you use the Excel formula =VLOOKUP(502, A2:K6, 9, FALSE) on Table 1, what will it return?

2 (value in the “OR” column for Patient ID 502)
43 (value in the “Age” column for Patient ID 502)
ID810 (value in the “Surgery type” column for Patient ID 502)
1 (value in the “Day” column for Patient ID 502)

VLOOKUP Function

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value to search for in the first column of the table_array.
table_array: The range of cells that contains the data.
col_index_num: The column number in the table_array from which to retrieve the value (indexed from 1).
range_lookup: A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

M1.3: Average calculation

If you enter the formula =AVERAGE(G2:G6) in Excel considering the data in Table 1, 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

M1.4: Conditional counting

Considering the data in Table 1, what will the formula =COUNTIF(D2:D6, 1) count?

The number of surgery records assigned to OR 1
The number of patients who had surgery in the 1st week
The number of patients with a registered surgery duration of 1 hour
The number of patients with a surgery type ID starting with 1

COUNTIF Function

COUNTIF(range, criteria)

range: The range of cells that you want to apply the criteria to.
criteria: The condition that determines which cells to count.

M1.5: Total sum

If you use =SUM(G2:G6) in the dataset presented in Table 1, what are you summing?

The total number of surgeries
The total expected surgery duration for the selected patients
The total patient ages
The total number of patients scheduled in OR 1

M1.6: Multiple criteria sum

Considering the data in Table 1, 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.

M1.7: Multiple criteria counting

If you use the formula =COUNTIFS(D2:D6, 1, C2:C6, 1) in the dataset presented in Table 1, what is being counted?

The number of surgeries that took place in OR 1 during Week 1
The number of surgery records assigned to OR 1 on Day 1
The number of male patients scheduled in OR 1 during Week 1
The number of surgeries that had an expected duration of 1 hour

COUNTIFS Function

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

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.

M1.8: Total surgery duration

Considering the data in Table 1, you want to calculate the total expected surgery duration for male patients over 50 years old. Which formula explicitly applies both conditions and remains correct if the dataset also contains other genders?

=SUMIFS(F2:F6, E2:E6, "male", G2:G6, ">50")
=SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50")
=COUNTIFS(E2:E6, "male", F2:F6, ">50")
=SUMIF(F2:F6, ">50", G2:G6)

M1.9: Surgery count

To count the number of surgeries performed in OR 1 for patients older than 60 in Table 1, which formulas give the correct result for this dataset? (Multiple answers may be correct.)

=COUNTIFS(D2:D6, 1, F2:F6, ">60")
=SUMIFS(D2:D6, D2:D6, 1, F2:F6, ">60")
=COUNTIFS(E2:E6, "male", F2:F6, ">60")
=COUNTIF(F2:F6, ">60")

M1.10: Surgery duration

Considering Table 1, you need to find the total registered surgery duration for surgeries in Week 2. Which formula would be correct?

=SUMIFS(J2:J6, B2:B6, 2)
=SUMIFS(G2:G6, B2:B6, "Week 2")
=COUNTIFS(B2:B6, 2)
=SUMIF(B2:B6, 2, H2:H6)

M1.11: Substring extraction 1

You want to extract the first 6 characters from the patient name in cell A2 (“Leslie Fields”) from Table 2. Which formula would you use?

=LEFT(A2, 6)
=RIGHT(A2, 6)
=MID(A2, 6, 6)
=TEXT(A2, 6)

M1.12: Substring extraction 2

In cell A3 from Table 2, the patient name is “Arnold Ross”. You want to extract the last 4 characters of the name. Which formula would you use?

=LEFT(A3, 4)
=RIGHT(A3, 4)
=MID(A3, 4, 4)
=TEXT(A3, 4)

M1.13: Concatenating values

You want to append “is male” to the full patient name in A2 in Table 2. Which formula will correctly combine these values?

=CONCAT(A2, " is ", "male")
=A2 & " is " & B2
=CONCAT(A2, " ", "is", " ", B2)
All of the above

M1.14: Finding a space

You want to find the position of the space in the patient name in cell A2 (“Leslie Fields”) in Table 2. Which formula(s) will return the correct position?

=SEARCH(" ", A2)
=FIND(" ", A2)
=LEFT(A2, 1)
=MID(A2, 1, 1)

FIND and SEARCH Functions

FIND(find_text, within_text, [start_num]): Returns the starting position of a substring within a text string.
SEARCH(find_text, within_text, [start_num]): Returns the starting position of a substring within a text string (case-insensitive).
LEFT(text, [num_chars]): Returns the leftmost characters from a text string.
MID(text, start_num, num_chars): Returns a specific number of characters from a text string, starting at a specific position.

M1.15: Substring extraction using spaces

You want to extract the middle word “Banks” from the patient name in cell A5 from Table 2, where the name is “Doyle Banks Junior”. Which of the following formulas would achieve this? (Note: Multiple answers may be correct.)

=MID(A5, 7, 5)
=MID(A5, FIND(" ", A5) + 1, FIND(" ", A5, FIND(" ", A5) + 1) - FIND(" ", A5) - 1)
=MID(A5, 7, 13)
All of the above

M1.16: Length of first name

You want to calculate the total number of characters in the patient’s first name in cell A2 (“Leslie Fields”) from Table 2. Which formulas will give the correct result? (Note: Multiple answers may be correct.)

=LEN(LEFT(A2, FIND(" ", A2) - 1))
=LEN(LEFT(A2, FIND(" ", A2)))
=LEN(MID(A2, 1, FIND(" ", A2) - 1))
=LEN(A2) - LEN(A2) + FIND(" ", A2)

M1.17: Adding Mr. or Miss based on gender

In sheet patient_data, you have patient names in column A and their genders in column B. Assume every value in column B is either “male” or “female”. You want to add “Mr.” or “Miss” before each patient’s name in column A, depending on their gender. Which formulas can you use in cell C2? (Multiple answers may be correct.)

=IF(B2="male", "Mr. " & A2, "Miss " & A2)
=IF(B2="female", "Mr. " & A2, "Miss " & A2)
=IF(B2="male", "Miss " & A2, "Mr. " & A2)
=IF(B2="female", "Miss " & A2, "Mr. " & A2)

M1.18: User input with case insensitivity and extra spaces

Answer this question on paper. It uses only the 2 tables printed here, so you do not need to open or build anything in Excel.

You have a list of patient names in sheet patient_data presented in Table 2, and the user will input a patient name in a specific cell in an empty worksheet named Sheet3 (see Table 3). Sheet3 is not part of the chapter workbook; it holds only the 2 labels shown below. You want to perform a case-insensitive search based on the user’s input and return the exact match from patient_data, regardless of whether the input is in upper case, lower case, mixed case, or has extra ordinary spaces.

Table 3: Sheet3 sheet.

	A	B
1	User Input	Result
2

The user will type a patient name (e.g., “leslie fields”, “Leslie Fields”, or “LESLIE FIELDS”) in cell A2 on Sheet3. The formula in cell B2 should match the name without regard to case or extra spaces, then return the spelling and capitalization stored in column A of patient_data.

For example: If the user types “leslie fields” or “LESLIE FIELDS” into A2, the result in B2 should return “Leslie Fields” from patient_data.

Which formula would you use in cell B2 on Sheet3 to achieve this?

=XLOOKUP(A2, patient_data!A2:A6, patient_data!A2:A6, "Not Found", 0)
=XLOOKUP(LOWER(A2), LOWER(patient_data!A2:A6), patient_data!A2:A6, "Not Found", 0)
=XLOOKUP(TRIM(LOWER(A2)), TRIM(LOWER(patient_data!A2:A6)), patient_data!A2:A6, "Not Found", 0)
=XLOOKUP(TRIM(A2), patient_data!A2:A6, LOWER(patient_data!A2:A6), "Not Found", 0)

Functions Used

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode]): Searches a range or an array, and returns an item corresponding to the first match found.
- lookup_value: The value to search for.
- lookup_array: The range to search for the value.
- return_array: The range to return the value from.
- if_not_found: The value to return if no match is found.
- match_mode: The type of match to perform (0 for exact match).
TRIM(text): Removes extra spaces from text.
- text: The text from which to remove spaces.
LOWER(text): Converts text to lowercase.
- text: The text to convert to lowercase.
UPPER(text): Converts text to uppercase.
- text: The text to convert to uppercase.

M1.19: Splitting full name into first name and surname

You have a list of patient names in patient_data sheet presented in Table 2, column A, where each name consists of a first name and a surname separated by a space. You want to extract the first name into one column and the surname(s) into another.

The formulas must work when copied through all names in A2:A6. For example, they should return the following values for “Leslie Fields” in A2:

First Name in C2: Leslie
Surname(s) in D2: Fields

Use “Arnold Ross” in A3 as a second check. The copied formulas must return Arnold and Ross.

Which combination of formulas should you use in C2 and D2 to extract the first name and surname from A2 and continue working when copied down?

=LEFT(A2, FIND(" ", A2) - 1) for First Name and =RIGHT(A2, LEN(A2) - FIND(" ", A2)) for Surname
=RIGHT(A2, FIND(" ", A2) - 1) for First Name and =LEFT(A2, LEN(A2) - FIND(" ", A2)) for Surname
=LEFT(A2, LEN(A2) - FIND(" ", A2)) for First Name and =RIGHT(A2, FIND(" ", A2) - 1) for Surname
=MID(A2, 1, FIND(" ", A2)) for First Name and =LEFT(A2, FIND(" ", A2)) for Surname

Functions Used

LEFT(text, num_chars): Returns the first characters in a text string.
- text: The text string from which to extract characters.
- num_chars: The number of characters to extract from the left of the text.
RIGHT(text, num_chars): Returns the last characters in a text string.
- text: The text string from which to extract characters.
- num_chars: The number of characters to extract from the right of the text.
FIND(find_text, within_text): Returns the starting position of one text string within another.
- find_text: The text you want to find.
- within_text: The text in which you want to search for find_text.
LEN(text): Returns the number of characters in a text string.
- text: The text string for which you want to calculate the length.
MID(text, start_num, num_chars): Returns a specific number of characters from a text string, starting at the position you specify.
- text: The text string containing the characters you want to extract.
- start_num: The position of the first character you want to extract.
- num_chars: The number of characters you want to extract.