
Demonstrate how analytics unlocks hidden insights by narrating enigma decryption, Bayes theorem, and data-driven prediction, turning complex codes into actionable healthcare analytics.
Install the real statistics add-in for Excel by downloading from realstatistics.com, unblocking the file, and enabling solver, then load it from the add-ins menu to access data analysis tools.
Explore how revenue cycle management links appointment scheduling, patient registration, and pre-registration for procedures. Learn how insurance eligibility and physician assignment drive smooth care delivery.
Explain insurance eligibility using a bank balance analogy, comparing access options like website, SMS, WhatsApp, and branch, then show hospitals validating coverages via clearinghouses and EDI 270/271 with Medicare ABN.
Understand the admissions flow, including registration versus admission and the clinical data collected, then explore financial counseling at the front desk for ballpark costs and NGO referrals.
Track the outpatient journey in the OPD from appointment and registration to nurse intake, vitals, diagnosis, and related tests and prescriptions.
Compare the inpatient department to the OPD, detailing ICU monitoring, hourly vitals and SpO2, arterial blood gas tests, NIV or ventilator decisions, and discharge summaries highlighting Indian versus US practices.
Explore healthcare codes and terminology standards such as ICD, SNOMED, LOINC, RxNorm, NDC, and NPH, and data transmission formats HL7 and FHIR across Epic, Meditech, Cerner.
Learn how medical transcription turns physician notes into digital records and how medical coding with ICD diagnosis and procedure codes standardizes billing and drives revenue cycle management.
Explain the back office of revenue cycle management, from entering charges and hold days to billing edits, claim submission via edi 837, and remittance advice (edi 835) to accounts receivable.
Identify and map source systems—from emr platforms like meditech, epic, cerner to lab, radiology, and billing—using hl7 concepts and dicom data to guide data provenance before visualization in healthcare analytics.
Identify source systems and define the data extraction approach for health care analytics. Examine standards such as HL7, FHIR, CCD, Dicom, and HIPAA, with an HL7 example of structured data.
Identify your KPIs and build a logical data model before analysis to guide data selection. Distinguish master data from transactional data to shape the database and align stakeholders.
Learn how to load data while building the data model in parallel and use healthcare integration tools like Mirth, Cloverleaf, and Rhapsody to read HL7, EDI, CCD, and FHIR standards.
Explore healthcare data by analyzing insights and foresights using charts in Tableau or Power BI, and extend with forecasting, prediction, and text mining in R and Python.
Review the patient journey and the end-to-end data analytics process, from identifying source systems to extracting, cleaning, standardizing, transforming, and loading data, then building data models and generating insights.
Turn raw data from source systems into actionable insights using descriptive, diagnostic, predictive, and prescriptive analytics, illustrated by the lion and fox and Gartner's ascendancy model.
Explore cognitive analytics using real time data from diverse media—text, images, video, and audio—to generate actionable insights within the Gartner Ascendancy model extension.
Explore descriptive analytics through an appointment data example, showing how to count recent appointments and break them down by specialty and gender to reveal patterns.
Explore diagnostic analytics to uncover why patient appointments are cancelled and why satisfaction scores lag, using appointment examples to identify root causes and improvement opportunities.
Forecast patient demand with predictive analytics by week or month, accounting for seasonality and regional trends. Predict volumes by area, department, physician, inpatients, lab tests, corporate packages, and medication requests.
Explore prescriptive analytics and optimization by turning forecasts into recommended actions. Use personalized reminders to reduce no-shows and restructured schedules to reduce wait times.
Explore descriptive, diagnostic, predictive, and prescriptive analytics in radiology and medical coding, including image volumes by modality, turnaround times, top codes, and forecasted wait times.
Explore artificial intelligence, machine learning, and deep learning, featuring examples from healthcare analytics like forecasting appointment volumes with ARIMA and deploying chatbots powered by neural networks.
Discover how supervised learning uses labeled data to train a model, split data into training and testing sets, and evaluate performance through final evaluation.
Explore unsupervised learning by clustering data without labels, grouping images by observable features like citrus fruits or vitamins, and by basic user info such as age and gender.
Explore reinforcement learning with real-world examples like classroom point systems, group competition, and robot training using positive and negative reinforcement.
Sometimes you might get an #Name error when you open the Output Excel File (Analysis file) I shared. I have explained how to fix this error in this short video.
Explore how eugenics relates to heredity by examining Francis Galton's 19th-century analysis of family height data, illustrating regression to the mean and the beginnings of simple linear regression.
Explore the logic of simple linear regression and the best fit line that minimizes distance to points, using y = mx + c to predict outcomes.
Apply linear regression to insurance charges data to predict patient charges using features such as age, BMI, number of children, smoking, and region, while exploring multiple methods for best results.
This lecture demonstrates simple linear regression in Excel, using age to predict insurance charges, including scatter plots, trend lines, and interpreting the regression equation and R-squared.
Apply simple linear regression to compute y hat using y = 257.72 x + 3165.9, where x is age and y is the predicted charges.
Apply method 2 of simple linear regression by plotting age against log of charges on a scatter plot, add a trend line and equation, and compare r-squared to the earlier method.
The lecture demonstrates simple linear regression to predict healthcare charges by modeling the log base-10 of charges as a function of age, then exponentiating to obtain predicted charges.
Apply simple linear regression using method 3 by analyzing age versus average charges, creating a pivot chart, copying values, and plotting a scatter plot with a trend line.
Calculate predicted charges using simple linear regression method 3 with the formula y = mx + c, applying it to x values to obtain y hat as predicted charges.
Discover how multiple linear regression uses all available variables—age, gender, BMI, children, smoker, and region—to predict charges, and learn about setting up dummy variables.
Turn categorical data into dummy variables to predict charges from age, BMI, children, gender, smoker, and region by encoding gender, smoker, and region with dummy columns and dropping one column.
Handle missing values in healthcare data by deleting rows or columns, imputing with mode or mean, using machine learning and domain knowledge to preserve distribution for BMI, gender, and region.
Apply dummy variables to enable multiple linear regression in Excel by creating gender and smoker columns, using if formulas with absolute references, and extending them down the data.
Add a smoker dummy variable to a multiple linear regression using yes/no categories. Copy formulas from the gender dummy setup and extend the approach to the region variable.
Add region dummy variables for four regions: west, southeast, northwest, and northeast, copy the formula from cell H2, drag it across, delete unwanted columns, and proceed to multiple linear regression.
Apply multiple linear regression in Excel by preparing base data: remove text and categorical columns, copy data, paste values to strip formulas, and duplicate a base sheet for regression analysis.
Apply multiple linear regression using the Excel add-in, selecting input ranges for x and y (charges), including the intercept, and interpreting R-squared results to evaluate model performance.
Learn to interpret Excel regression output for healthcare analytics, focusing on fit metrics such as multiple R, r square, adjusted r square, standard error, and anova results.
Compare models using Akaike information criteria (AIC), corrected AIC for small samples, and Bayesian information criterion (BIC), where lower values indicate better fit in multiple linear regression.
Explore the Anova output, including degrees of freedom, sum of squares, mean square, and f statistic, to assess overall model significance and interpret the p value.
Learn to interpret the intercept in the coefficient table, including the standard error, t stat, p value, confidence interval, and multicollinearity via VIF, and estimate the prediction range.
Explore multicollinearity in a house price model. Analyze how bedrooms and square footage affect price using variance inflation factor (VIF), coefficients, p-values, and confidence intervals to enable predictions.
Identify statistically significant variables by examining p-values in the coefficient table, highlight results with formatting, and discuss using the model for prediction, similar to y equals mx plus c.
Learn to calculate predicted values in multiple linear regression using coefficients and intercept in Excel, applying y = sum Mi Xi, with age, BMI, children, gender, smoker, and region.
Perform a second iteration of multiple linear regression, remove non-significant variables such as southwest and southeast, and compare the R square to confirm region lacks statistical significance in predicting charges.
Use the Excel add-in to compute descriptive statistics for age, including mean, median, mode, standard deviation, skewness, kurtosis, and normality, then extend to other variables.
Generate and compare descriptive statistics for all variables using the add-in, with optional box plots to visualize distributions; note that zeros cause geometric and harmonic means to be undefined.
Explore homoscedasticity and heteroskedasticity in regression, assess residuals from observed versus predicted values, and apply weighted linear regression when residuals vary across the data.
Explore homoskedasticity using a housing area vs heating cost data set in Excel, creating scatter plots with a trend line, and analyzing residuals against predicted values for constant variance.
Explore heteroskedasticity by linking experience to salary, plotting a scatter chart with a trend line, and using Bruce pagan test and White's test to detect non-constant dispersion.
Apply Breusch-Pagan test and White's test in Excel to detect heteroskedasticity in regression using sample data. Interpret p-values to assess homoscedasticity or heteroscedasticity and consider weighted linear regression when needed.
Learn weighted linear regression by assigning weights to data points, where science and math have higher weights than English, and apply this in Excel real statistics with insurance data.
Learn weighted linear regression and how to assign weights, including age-based domain weights and inverse-residual weights in insurance data.
Check heteroskedasticity in insurance charges by plotting predicted vs residuals, confirm with the Bruce Pagan test, then apply weighted linear regression using age ranges and the residual-inverse method.
apply weighted linear regression on age-based data by creating a weight column, assigning weights with if statements, and using OLS regression through the origin to model charges.
Apply prediction in weighted linear regression by copying coefficients, pasting values in the lr one tab, and computing predicted values with sum product to compare outputs.
Apply weighted linear regression in Excel using method #2 by building a weights column from one over residual squared, then perform OLS regression through the origin.
Develop logistic regression intuition through a personal yoga story, framing a binary outcome: weight loss, and probability for healthcare IT data analytics.
Observe how walking distance data informs logistic regression to predict the probability of weight loss, illustrated by the sigmoid curve and a distance-versus-outcome plot.
Explore the confusion matrix in logistic regression by converting predicted probabilities into yes/no outcomes with a 0.5 threshold and assess false positives and false negatives.
Analyze the confusion matrix’s four quadrants by comparing actual and predicted outcomes, identifying true positives, false negatives, true negatives, and false positives, and note accuracy and rates like sensitivity.
Explore the confusion matrix in logistic regression, analyzing its four quadrants—true positives, false negatives, false positives, true negatives—and compute metrics such as true positive rate, false positive rate, and accuracy.
Explore logistic regression on prostate cancer data to predict diagnosis using tumor features such as radius, texture, perimeter, area, smoothness, compactness, symmetry, and fractal dimension.
Apply logistic regression and descriptive statistics to a prostate cancer data set, performing data cleaning and recoding diagnosis labels in Excel, then interpret data quality.
Identify missing values in descriptive statistics for fractal dimension and compactness using conditional formatting, then impute with mean (0.124597 for compactness, 0.06481 for fractal dimension) instead of deleting records.
Explore imputing missing data in healthcare analytics by using pivot tables to compute mean values by diagnosis, applying domain knowledge for benign and malignant diagnoses using fractal dimension and compactness.
Impute missing values by diagnostic result using mean values for compactness and fractal dimension, then apply logistic regression to the data.
Apply logistic regression to clean data by making the diagnostic result the last column, then review the ROC, confusion matrix, accuracy, and the intercept, coefficients, and p-values.
Explore the ROC curve for logistic regression, interpreting true and false positive rates, thresholds from 0 to 1, and the confusion matrix to assess model performance and AUC.
Analyze the confusion matrix and classification table for a prostate cancer model, showing true positive rate, false positive rate, accuracy, and how cutoff changes affect results.
Explore logistic regression outputs, including observed vs predicted values, log likelihood, accuracy, confusion matrix, and the coefficient table with exp b and p values.
Explore logistic regression outputs, including ROC, AUC, chi-square tests, and p-values, to assess model significance on insurance data. Identify compactness and fractal dimension as the two statistically significant features.
Compute y hat with y = m1 x1 + m2 x2 + m3 x3 in Excel, copy and transpose coefficients, then apply the intercept and features like radius and texture.
Apply the regression formula by computing the sum product of X1 through X8 with M1 through M8, add the intercept, and produce the y hat for all rows.
Calculate the predicted output using the logistic function P = 1/(1+e^{-y}) where y = mx + c, and apply the Excel formula to obtain consistent probabilities, such as 0.9997.
Apply predicted formula in logistic regression by repeating the coefficient-based calculation to compute predicted value for given radius, texture, and perimeter; explore using compactness, fractal dimension, and diagnosis as predictors.
Explore logistic regression iteration 2 using compactness, fractal dimension, and diagnosis result in Excel real statistics, and compare its 81% accuracy to the prior 89%.
Explore nominal versus ordinal data using a restaurant seating and rating example, highlighting no inherent order for seating and an ordered scale for ratings, with multinomial and ordinal regression applications.
Explore multinomial regression on asthma data to predict medication categories (none, inhaler, controller/med) from age, gender, smoking status, asthma diagnosis, and peak flow, after data cleaning.
Prepare a health data set in excel by removing patient id, creating an interim sheet, and encoding gender, asthma diagnosis, and smoking status as dummy variables for multinomial logistic regression.
Continue cleaning data by encoding gender, asthma, and smoking status into numeric indicators using three binary columns and if statements for multinomial logistic regression.
Learn how to clean data for multinomial logistic regression by recoding the medication column into three categories within the same column, and prepare gender and smoking status for modeling.
Finalize the columns by deleting unnecessary ones, converting categories to binary values, and preparing the final data sheet. Then apply multinomial regression on the cleaned data.
Apply multinomial logistic regression in the real statistics add-in to analyze cleaned data from age to medication, configure input ranges and independent variables, and interpret the output for prediction.
Explore multinomial logistic regression outputs, including input data from age to peak flow, three outcome groups, counts, coefficients, and the R square value with chi-square significance, and predict values.
Apply multinomial logistic regression to predict category outputs from input data, using the mlogit function and variables like age and peak flow, and interpret the predicted values.
Explore multinomial logistic regression on primary tumor data from UCI to predict the location class across more than three categories, using 339 records and 17 attributes, including age and gender.
Explore data cleaning in Excel and prepare a four-class dataset for multinomial logistic regression, using UCI notepad data and class mappings for stomach, pancreas, ovary, and breast.
Clean and recode tumor data for multinomial logistic regression by converting gender to binary, preserving age group values 1, 2, and 3, and relabeling the class variable to 0–3.
Clean and recode the tumor data by converting class values 5,11,18,22 to 0,1,2,3, then prepare for multinomial logistic regression in Excel real statistics.
Apply multinomial logistic regression to tumor data using the real statistics add-in, examine multiple coefficient sets for four outcomes, and assess model fit with r-squared and significance tests.
Apply multinomial logistic regression to predict four outcomes from tumor data, calculate predicted values, and determine the predicted category by identifying the maximum probability and using Xlookup.
Explore ordinal regression on obesity data by modeling ordered obesity categories from underweight to obese and predicting linked physical activity levels, using age, gender, height, weight, and BMI as features.
Clean obesity data in Excel by converting gender to binary codes and mapping obesity categories with vlookup, then prepare for ordinal regression.
Clean the data by keeping only numeric columns, removing the text values column, and deleting features like gender and obesity category, then apply ordinal regression to the city target.
Apply ordinal regression to a prepared data set, select input columns and the target, and compare it with multinomial regression using 1–4 versus 0–3 values, reviewing coefficients and convergence.
Diagnose an ordinal regression error by examining the covariance output, identify multicollinearity with VIF among height, weight, and BMI, remove BMI, and demonstrate BMI calculation before rerunning.
Calculate BMI from height and weight by converting height from centimeters to meters and dividing weight in kilograms by the squared height, then reapply ordinal regression.
Reapply ordinal regression without BMI data, updating the input range from age to obesity and removing the BMI column, with a covariance matrix and 96% correct.
Explore ordinal regression to compute predicted obesity categories from five independent variables, using the logit formula and coefficients, and validate results with a pivot table confusion matrix.
Create a four-category confusion matrix for ordinal regression data by computing the max value, deriving predicted and actual categories with xlookup, and using a pivot table to count correctness.
Prepare an ordered physical activity data set and apply ordinal regression to predict activity levels, then compute predicted values and the confusion matrix.
Apply ordinal regression to predict physical activity level on new data using five independent variables, and review outputs such as coefficients, covariance matrix, and the predicted output.
Learn to calculate predicted values for ordinal regression in a spreadsheet by applying coefficients to independent variables A–E using the logit function and preparing a confusion matrix.
Create a confusion matrix for ordinal regression using Excel, computing max values, predicted and actual categories, and a pivot table to summarize correct predictions.
Are you Interested in learning how to apply some machine learning algorithms using Healthcare data and that too using Excel? Yes, then look no further.
This course has been designed considering various parameters. I combine my experience of twenty two years in Health IT and twelve years in teaching the same to students of various backgrounds (Technical as well as Non-Technical).
In this course you will learn the following:
Understand the Patient Journey via the Revenue Cycle Management Workflow - Front, Middle and Back Office
The Data Visualization Journey - Moving from Source System to creating Reports
Understand Descriptive, Diagnostic, Predictive and Prescriptive Analytics
At present I have explained below Algorithms
Simple Linear Regression | Multiple Linear Regression | Weighted Linear Regression |
Logistic Regression | Multinomial Regression |
Ordinal Regression |
KNN Classification | KMeans Clustering
Classic Time Series | ARIMA |
Some of the concepts key explained are listed below
Homoscedasticity vs Heteroskedasticity
Breusch-Pagan & White Test
Confusion Matrix
Nominal vs Ordinal Data
AUC & ROC Curve
ACF & PACF in Time Series
Differencing in Time Series
Healthcare Datasets to create the algorithms.
I have listed a the healthcare datasets used below
Health Insurance Data
Covid Cases
Asthma Data
Obesity
Member Enrollment
Pharma Sales
Prostate Cancer
Breast Cancer
Maternal Health Risk
**Course Image cover has been designed using assets from Freepik website.