Question

Can I take a real public health dataset and build an interactive Excel dashboard that lets someone look up any hospital in the country and see how it stacks up against state and national averages?

Hypothesis

I figured the data would show big regional differences in hospital quality and that ownership type would have a real effect on average ratings. I wanted to see if Veterans Health or non profit hospitals would outperform for profit ones.

Approach

Pulled CMS Hospital General Information data covering 5,432 hospitals across the country. Cleaned the data down to 19 useful columns and built a calculated Performance Score using the count of mortality, safety, and readmission measures where each hospital beat or trailed national comparisons. Set up a Hospital Lookup sheet with a dropdown of every hospital name, then used XLOOKUP to pull the full profile and AVERAGEIF to calculate state level benchmarks on the fly. Added conditional formatting so the comparison column lights up green when a hospital beats national averages and red when it falls below. Built a separate Performance Dashboard with four pivot tables (top hospitals, performance by state, ratings by state, performance by ownership type), KPI cards, and three slicers so the user can filter the entire dashboard by state, ownership, or hospital type.

Result

Veterans Health Administration hospitals came out on top with the highest average star rating (4.16), beating non profit and government hospitals across the board. Proprietary (for profit) hospitals had the lowest average rating at 2.80. Utah had both the highest average star rating and the highest performance score of any state, which surprised me. The dashboard also exposed something I would not have caught without the comparison view. Many hospitals that look strong on raw star rating actually trail on the more granular measure based scores, and the reverse is true too. Looking at multiple metrics together tells a stronger story than any one number on its own.

Question

Which hierarchical clustering method gives the cleanest structure on the wine dataset, and which linkage strategy works best?

Hypothesis

I figured one of the AGNES linkage methods would come out on top since they each handle distance differently, but I wanted to see how the divisive method DIANA would stack up too.

Approach

Scaled the wine data first so the distances would be comparable across features. Ran AGNES with four linkage methods (single, complete, average, and Ward) and compared their agglomerative coefficients. Then ran DIANA on the same data to see how a divisive approach would handle it. Cut both trees at k=3 to compare cluster sizes side by side.

Result

AGNES with Ward linkage had the highest agglomerative coefficient, which made it the most cohesive method on this dataset. It also gave more balanced clusters of 69, 58, and 51 wines, while DIANA produced sizes of 91, 38, and 49. I don't think K-Means or DBSCAN would be a better fit here either since K-Means is sensitive to outliers and DBSCAN is built more for finding density. For this dataset, AGNES Ward was the right call.