Hospital Management with Records, Beds & Billing

A hospital management sheet tracks patients, admissions, bed occupancy, and billing in one workbook. You build it in Excel and use Copilot and ChatGPT for the lookup and billing formulas.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It records patient visits, assigns doctors, tracks occupied beds, and totals billing.
• Where it is used: Clinics and small hospitals that need a light records system.
• What you learn: XLOOKUP, COUNTIF, SUMIF, bed occupancy, and AI prompt writing.

Features

• Patient record lookup by ID
• Doctor assignment counts
• Bed occupancy rate
• Billing total per patient
• AI-written discharge summary prompt

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A patient ID list and bed count

Folder Structure

hospital-management.xlsx
│
├── Sheet: Patients   (PatientID, Name, Doctor, Admit, Discharge)
├── Sheet: Billing    (PatientID, Amount)
├── Sheet: Beds       (BedID, Status)
└── Module: HospMacros (optional VBA)

• Patients: admission records.
• Billing: charges per patient.
• Beds: bed status list.
• HospMacros: optional helpers.

Step-by-Step Development

Step 1 – Patient table and lookup

What are we building?

A Pat table and a search that returns a patient's doctor and status.

Why do we need it?

Staff need instant record access.

How do we build it?

=XLOOKUP(B1, Pat[PatientID], Pat[Doctor], "Not found")
=XLOOKUP(B1, Pat[PatientID], Pat[Name], "Not found")

• What it does: finds the patient by ID and returns doctor or name.
• Important line: the "Not found" argument handles missing IDs.
• Why needed: fast, safe lookup.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an XLOOKUP that returns a patient's doctor from PatientID, showing Not found if missing."

Step 2 – Doctor workload

What are we building?

A count of patients per doctor.

Why do we need it?

Helps balance assignments.

How do we build it?

=COUNTIF(Pat[Doctor], A2)

• What it does: counts patients assigned to the doctor in A2.
• Why needed: each doctor gets a load number.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write COUNTIF that counts patients where Doctor equals A2."

Step 3 – Bed occupancy

What are we building?

A percentage of occupied beds.

Why do we need it?

Shows capacity at a glance.

How do we build it?

=IFERROR(COUNTIF(Beds[Status], "Occupied")/COUNTA(Beds[BedID]), 0)

• What it does: occupied beds divided by total beds.
• Important line: IFERROR returns 0 when there are no beds listed.
• Why needed: clear occupancy percent.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write bed occupancy as occupied beds divided by total beds, with error handling."

Step 4 – Billing total and AI note

What are we building?

A total billed per patient and an AI discharge prompt.

Why do we need it?

Finance needs totals; doctors need notes.

How do we build it?

=SUMIF(Billing[PatientID], B1, Billing[Amount])

• What it does: sums all charges for the patient in B1.
• Why needed: one number per patient.

Use AI (Copilot & ChatGPT)

Paste a patient's record into ChatGPT:

"Write a one-paragraph discharge summary from this admission record."

Complete Source Code

Key formulas

=XLOOKUP(B1, Pat[PatientID], Pat[Doctor], "Not found")
=COUNTIF(Pat[Doctor], A2)
=IFERROR(COUNTIF(Beds[Status], "Occupied")/COUNTA(Beds[BedID]), 0)
=SUMIF(Billing[PatientID], B1, Billing[Amount])

AI prompts

ChatGPT: "XLOOKUP doctor from PatientID, Not found if missing."
Copilot: "COUNTIF patients where Doctor equals A2."
ChatGPT: "Bed occupancy occupied/total with error handling."
ChatGPT: "Discharge summary from this admission record."

Running the Project

1. Open hospital-management.xlsx.
2. Enter patients, beds, and billing.
3. Search by ID; occupancy and billing update.
4. Expected output: live records, occupancy, and totals.

Testing

Test Case 1

Input: Patient P005 assigned to Dr. Lee. Expected Output: Lookup shows "Dr. Lee". Explanation: XLOOKUP matches the ID.

Test Case 2

Input: 20 beds, 15 occupied. Expected Output: Occupancy 75%. Explanation: 15/20.

Test Case 3

Input: P005 billed 100 and 50. Expected Output: Total 150. Explanation: SUMIF adds both charges.

Common Errors

XLOOKUP #N/A

Problem: ID not found though it exists. Reason: Extra spaces in the ID. Solution: Trim the search cell.

Occupancy error

Problem: #DIV/0!. Reason: No beds listed. Solution: IFERROR handles it as shown.

Billing double count

Problem: Total too high. Reason: PatientID duplicated in Billing. Solution: Keep one row per charge; SUMIF sums all matches.

Improvements

• Add diagnosis and medicine columns
• Use Power Query to import lab results
• Power Automate to alert empty beds
• Ask ChatGPT for readmission risk
• Export billing to PDF

Challenge Exercises

1. Add an average stay formula (Discharge minus Admit).
2. Write a formula for total billing per doctor.
3. Use ChatGPT to write VBA that marks a bed Occupied on admission.
4. Build a chart of admissions per day.
5. Let ChatGPT flag patients with stay over 10 days.