Adding the binary entropy function to LibreOffice Calc

Lately at work I've been doing some data analysis in LibreOffice Calc that requires the binary entropy function. The function itself looks like H₂(x) =  − xlog₂(x) − (1 − x)log₂(1 − x), where 0log₂(0) is taken to be 0. It's this latter point that makes things a little tricky. LibreOffice Calc doesn't have this function built-in, sadly, and you have to explicitly guard for the case where x is 0 or 1, which is not easy to pull off inside a cell.

So I wrote a basic macro that implements it:

Function BINENT(x)
    If x = 0 Or x = 1 Then
        BINENT = 0
    ElseIf x > 0 And x < 1 Then
        BINENT = -(x*Log(x) + (1 - x)*Log(1 - x))/Log(2)
    Else
        BINENT = Null
    End If
End Function

To be able to use BINENT in Calc, go to the “Tools” menu, “Macros”, “Organize Macros”, and select “LibreOffice Basic...”. You'll be shown a window with a library hierarchy on the left. Under the “My Macros” collection, select the “Standard” library, then click the “New” button on the right. This will open up the editor. Copy and paste the BINENT code above into the editor window, save it, then close the window.

Now you should be able to use BINENT in the cells of any spreadsheet just like any other single-parameter function. Of course, the same approach is used to add any function that isn't already built-in.

Comment to add? Send me a message: <brendon@quantumfurball.net>

← Previous | Next →