Entropy in Excel

This work presents the development of an Excel‑oriented calculator designed to compute the results of entropy analysis for data distributed across categories. The research focuses on histograms of arbitrary distribution laws and 2×2 contingency tables.

Research methods: entropy and information analysis of histograms with arbitrary distribution laws, as well as entropy analysis of 2×2 contingency tables.

Results:   The study proposes the use of entropy‑based methods for analyzing categorical data and describes the structure of the Excel‑oriented calculator created for this purpose. The calculator computes key entropy characteristics of histograms, including:

• histogram entropy
• histogram dispersion
• confidence intervals for entropy
• diversity information index

Additionally, the calculator performs pairwise comparisons of histogram entropies using the Hutcheson method, and calculates Hellinger and Kullback–Leibler distances between histograms, thereby complementing the chi‑square criterion. It also determines the informational correlation coefficient. Using statistical modeling, the correspondence between the Pearson correlation coefficient and the informational correlation coefficient is established.

For 2×2 contingency tables, the calculator evaluates the significance of interaction between row and column factors and computes the corresponding conditional entropies.

The proposed calculator addresses gaps in existing software tools and provides a practical means of applying entropy‑based methods to categorical data. The study demonstrates that entropy methods are particularly suitable when histograms represent arbitrary distribution laws.