How to calculate entropy change?

The entropy change formula is (x_ij*log₂ (x_ij)). To calculate the entropy change in Excel follow the steps below.

Preparation of the entropy data set

We are going to use dummy data. Suppose you have the following data.

Before calculating the entropy change you first have to find out the sum of each series.

Entropy calculation

Click on Cell A5 and write this formula =SUM (A2:A4). Select cell A and move the cursor to the bottom right of the cell. A black plus sign will appear. Drag the plus sign to cell C5 to copy the formula in this cell.

Now click on cell A6 and write the formula =LOG (A5, 2)

Select Cell A6 and bring your cursor to the bottom right of the cell. A black plus sign will appear. Now drag the black plus sign to Cell C6 to copy the formula in this cell. You have now calculated the entropy change in Excel.

Entropy Formula

Here's another simple formula to calculate entropy in Excel:

=-SUM(A2:A10*LOG2(A2:A10))

Here, A2:A10 represents the range of cells containing the probabilities of each item in the data set. The LOG2 function calculates the logarithm base 2 of each probability, and the SUM function adds up the results. Finally, the result is negated with the minus sign to get the entropy value.

Note that the probabilities should sum up to 1.0, and all probabilities should be positive values. If your data set contains negative or zero values, you will need to modify the formula accordingly.

Further reading: 
Logarithms in Excel
Antilog in Excel