Below is the embedded Google Sheets Dashboard. Scroll down further to view spreadsheet documentation and analysis details.

Spreadsheet Cleaning and Analysis

Google Sheets Workflow

Portfolio Data Note

This project uses fully synthetic patient-level fall data. Hospital names are used only as fictional labels for the exercise; the results do not describe actual patients, hospitals, or health-system performance.

Workflow Setup

I imported both raw CSV files into Google Sheets and created working copies so the original synthetic source data remained unchanged.

I then separated the workflow into tabs for cleaning formulas, lookup logic, cleaned values, pivot tables, summary statistics, and dashboard charts.

Primary working tabs included:

• Fall_Incident_Log_working

• Lookup_Fall_reasons_working

• Fall_Incidents_Log_cleaned

• Analysis_Pivot_Tables

The cleaned output tab contained values only so subsequent analysis did not depend on working formulas or temporary calculations.

Initial Quality Checks

The source contained:

• 85 fall incidents

• 80 unique fictional patients

• No duplicate incident records

• No material whitespace issues identified during the initial trim check

• 14 unique raw hospital-name values representing four hospitals

Cleaning Step 1: Hospital Names

The hospital field contained 14 naming variations. I created a new Cleaned_Hospital_Name column and standardized them to four labels using case-insensitive regular-expression matching.

=IFS(

 REGEXMATCH(B2, "(?i)Northwestern|NM"), "Northwestern Memorial Hospital",

 REGEXMATCH(B2, "(?i)Rush"), "Rush University Medical Center",

 REGEXMATCH(B2, "(?i)UChicago"), "UChicago Medicine",

 REGEXMATCH(B2, "(?i)Advocate"), "Advocate Illinois Masonic",

 TRUE, "Other"

)

The resulting hospital categories were:

• Northwestern Memorial Hospital

• Rush University Medical Center

• UChicago Medicine

• Advocate Illinois Masonic

Cleaning Step 2: Fall Date and Time

The raw timestamps used more than one format.

After confirming that Google Sheets recognized the values as dates, I applied a consistent Date and Time display format.

I then derived two additional fields:

Fall_Hour

=HOUR(G2)

Fall_Day

=TEXT(G2, "dddd")

These fields were later used to analyze incident patterns by hour and day of week.

Cleaning Step 3: Patient Age

Before cleaning:

• Maximum age: 210

• Missing values: 2

• Minimum age: 58

• Mean age: 79.20

• Median age: 78

• Standard deviation: 16.94

The age of 210 was clearly implausible.

Because there was no underlying source record available to determine the intended age, I did not guess whether the value was supposed to represent 20, 21, or another age.

For the Google Sheets workflow, the two missing ages and the implausible age were replaced with the dataset median of 78 in a new Cleaned_Patient_Age field.

=IF(OR(ISBLANK(E2), E2>120), MEDIAN($E$2:$E$86), E2)

The SQL workflow handled age differently. Its master table retained the original values, later flagged the age of 210 as an anomaly, and excluded ages over 120 only from selected analyses.

Because of this difference, I do not describe the Sheets and SQL cleaning pipelines as identical.

Cleaning Step 4: Unit Type

The unit field already contained four consistent categories:

• Med-Surg: 39 incidents

• Geriatric Psych: 18 incidents

• Acute Rehab: 14 incidents

• ICU: 14 incidents

No recoding was required.

Cleaning Step 5: Morse Fall Scale

Initial review showed:

• Maximum: 120

• Minimum: 25

• Mean: 66.85

• Median: 65

• Standard deviation: 26.40

• Missing values: 1

All non-missing scores fell within the expected 0–125 scale range.

The one missing Morse score was left blank.

An early version of the Sheets analysis incorrectly grouped 84 incidents as high risk and 1 as unknown. That grouping did not properly separate scores from 25–45.

The validated risk grouping used in the final portfolio is:

• High Risk, 46+: 60 incidents

• Medium Risk, 25–45: 24 incidents

• Missing: 1 incident

Cleaning Step 6: Cognitive Status

Five records had no documented cognitive status.

I created Cleaned_Cognitive_Status and labeled these values Not Documented rather than inferring a clinical status.

Cleaning Step 7: TUG Mobility Fields

All pre-fall and post-fall Timed Up and Go values were complete.

I created two derived fields.

Absolute change in TUG time

=P2-O2

Relative change in TUG time

=(P2-O2)/O2

Because a longer TUG completion time represents worse performance, I describe this as an increase in TUG time or decline in mobility rather than saying the numeric TUG value itself "declined."

Cleaning Step 8: Provider Presence

The raw field contained:

• No: 64

• Yes: 12

• Missing: 9

I created Cleaned_Provider_Present and replaced missing values with Not Documented.

=IF(ISBLANK(S2), "Not Documented", TRIM(S2))

For final portfolio interpretation, I refer to this field as provider presence rather than witnessed/unwitnessed status because the source only indicates whether a provider was documented in the room.

Cleaning Step 9: Fall Reason Code

The expected lookup codes were:

• TOIL

• TRANS

• AMB

• MED

The source also contained MEDS, SLIP, UNK, blank values, and other unmatched values.

I created Cleaned_Fall_Reason_Code.

=IF(U2="MEDS", "MED", IF(ISBLANK(U2), "UNK", TRIM(U2)))

MEDS was corrected to MED.

Blank values were assigned UNK.

SLIP was intentionally not changed to TRANS because the source did not contain enough information to prove that the original value represented transfer slippage.

Cleaning Step 10: Injury Severity

The source contained:

• Missing: 36

• Minor: 26

• Major: 20

• Sentinel Event: 3

In Google Sheets, missing injury-severity values were labeled Not Documented.

I chose this wording because a missing field does not establish that no injury occurred.

Cleaning Step 11: Lookup Join

I imported the fall-reason lookup table into the incident workbook and used XLOOKUP to add both the reason description and the synthetic preventability tier.

Fall reason description

=XLOOKUP(V2, DIM_Fall_Reasons!$A$2:$A$5, DIM_Fall_Reasons!$B$2:$B$5, "Other / Unclassified Cause")

Preventability tier

=XLOOKUP(V2, DIM_Fall_Reasons!$A$2:$A$5, DIM_Fall_Reasons!$C$2:$C$5, "Tier 3")

Unmatched codes were therefore assigned:

• Other / Unclassified Cause

• Tier 3

The preventability tiers are synthetic project categories and are not a validated clinical classification system.

Cleaned Output

After validating the formulas, I copied values only into Fall_Incidents_Log_cleaned.

Raw fields, working columns, and temporary summary calculations were removed from this final analysis tab while the original source data remained unchanged in the raw/working tabs.

Spreadsheet Analysis

Injury Severity by Hospital

Advocate Illinois Masonic

• Major: 4

• Minor: 2

• Not Documented: 10

• Sentinel Event: 1

• Total: 17

Northwestern Memorial Hospital

• Major: 6

• Minor: 11

• Not Documented: 10

• Sentinel Event: 1

• Total: 28

Rush University Medical Center

• Major: 4

• Minor: 8

• Not Documented: 9

• Sentinel Event: 1

• Total: 22

UChicago Medicine

• Major: 6

• Minor: 5

• Not Documented: 7

• Sentinel Event: 0

• Total: 18

Overall

• Major: 20

• Minor: 26

• Not Documented: 36

• Sentinel Event: 3

• Total: 85

These are incident counts only and should not be interpreted as hospital fall rates because the dataset does not contain a patient-day or admission denominator.

Fall Reason by Provider Presence

Ambulation Loss of Balance

• Provider not present: 24

• Not documented: 1

• Provider present: 0

• Total: 25

Bed/Chair Transfer Slippage

• Provider not present: 10

• Not documented: 6

• Provider present: 6

• Total: 22

Medication-Induced Orthostatic Hypotension

• Provider not present: 4

• Not documented: 0

• Provider present: 6

• Total: 10

Other / Unclassified Cause

• Provider not present: 1

• Not documented: 1

• Provider present: 0

• Total: 2

Unassisted Toileting / Urgency

• Provider not present: 25

• Not documented: 1

• Provider present: 0

• Total: 26

Overall provider-presence status

• Provider not present: 64

• Not documented: 9

• Provider present: 12

TUG Change by Unit

Acute Rehab

• Average pre-fall TUG: 10.79 seconds

• Average post-fall TUG: 13.20 seconds

• Average increase: 2.41 seconds

• Average relative increase: 21.58%

Geriatric Psych

• Average pre-fall TUG: 25.86 seconds

• Average post-fall TUG: 40.47 seconds

• Average increase: 14.62 seconds

• Average relative increase: 56.10%

ICU

• Average pre-fall TUG: 12.24 seconds

• Average post-fall TUG: 16.14 seconds

• Average increase: 3.91 seconds

• Average relative increase: 29.08%

Med-Surg

• Average pre-fall TUG: 15.13 seconds

• Average post-fall TUG: 20.77 seconds

• Average increase: 5.65 seconds

• Average relative increase: 35.50%

Overall

• Average pre-fall TUG: 16.21 seconds

• Average post-fall TUG: 22.94 seconds

• Average increase: 6.73 seconds

• Average relative increase: 36.51%

Cognitive Status and Morse Fall Scale Score

• Alert & Oriented: 30 incidents, average Morse score 40.00

• Mild Confusion: 22 incidents, average Morse score 62.95

• Not Documented: 5 incidents, average Morse score 65.00

• Severe Dementia: 17 incidents, average Morse score 94.41

• Sun-downing: 11 incidents, average Morse score 103.64

• Overall: 85 incidents, average Morse score 66.85

Fall Reason by Unit

Unassisted Toileting / Urgency

• Acute Rehab: 0

• Geriatric Psych: 9

• ICU: 1

• Med-Surg: 16

• Total: 26

Ambulation Loss of Balance

• Acute Rehab: 6

• Geriatric Psych: 9

• ICU: 0

• Med-Surg: 10

• Total: 25

Bed/Chair Transfer Slippage

• Acute Rehab: 7

• Geriatric Psych: 0

• ICU: 6

• Med-Surg: 9

• Total: 22

Medication-Induced Orthostatic Hypotension

• Acute Rehab: 0

• Geriatric Psych: 0

• ICU: 7

• Med-Surg: 3

• Total: 10

Other / Unclassified Cause

• Acute Rehab: 1

• Geriatric Psych: 0

• ICU: 0

• Med-Surg: 1

• Total: 2

Repeat Fallers

MRN-100184

• 3 incidents

• Average Morse score: 70.0

• Maximum injury severity: Major

MRN-101961

• 2 incidents

• Average Morse score: 42.5

• Maximum injury severity score: 0

MRN-101239

• 2 incidents

• Average Morse score: 87.5

• Maximum injury severity: Major

MRN-100391

• 2 incidents

• Average Morse score: 57.5

• Maximum injury severity: Minor

The four repeat fallers accounted for 9 of the 85 incidents.

Additional Spreadsheet Findings

Falls by day of week:

• Sunday: 14

• Monday: 13

• Wednesday: 13

• Friday: 13

• Thursday: 12

• Tuesday: 10

• Saturday: 10

The highest individual hour was 03:00 with 6 incidents. Most other hours contained between 2 and 4.

Average pre-fall TUG by provider-presence category:

• Provider not present: 18.04 seconds

• Provider present: 11.06 seconds

• Not documented: 10.07 seconds

Synthetic preventability tiers:

• Tier 1: 48 incidents

• Tier 2: 25 incidents

• Tier 3: 12 incidents

All 20 Major injuries and all 3 Sentinel Events occurred when a provider was documented as not present.

Among the 12 provider-present incidents:

• 3 were Minor

• 9 had injury severity not documented

Google Sheets Dashboard KPIs

Total Falls

=COUNTA(Fall_Incidents_Log_Cleaned!A2:A86)

Average Morse Fall Score

=AVERAGE(Fall_Incidents_Log_Cleaned!I2:I86)

Provider-Not-Present Percentage

Originally labeled "Unwitnessed Falls":

=COUNTIF(Fall_Incidents_Log_Cleaned!P2:P86, "No") / COUNTA(Fall_Incidents_Log_Cleaned!P2:P86)

For the final portfolio, I use Provider Not Present % because this more accurately represents the source field.

Severe Injury Percentage

=(COUNTIF(Fall_Incidents_Log_Cleaned!R2:R86, "Major") + COUNTIF(Fall_Incidents_Log_Cleaned!R2:R86, "Sentinel Event")) / COUNTA(Fall_Incidents_Log_Cleaned!R2:R86)

Spreadsheet Findings

The most common fall reasons were unassisted toileting/urgency with 26 incidents and ambulation loss of balance with 25.

Geriatric Psych showed the largest average post-fall mobility decline, with TUG completion time increasing by 14.62 seconds, or 56.10% relative to baseline.

All 23 severe outcomes—20 Major injuries and 3 Sentinel Events—occurred when a provider was documented as not present. This is an association within the synthetic sample and should not be interpreted as proof of causation.

Fall mechanisms also differed by unit, supporting targeted rather than uniform prevention themes:

• Med-Surg: toileting

• Geriatric Psych: toileting and balance

• Acute Rehab: transfers

• ICU: medication-induced orthostatic hypotension

Day-of-week and hour-of-day counts were not different enough to support strong staffing conclusions from this dataset alone.