Lately at work I've been doing some data analysis in LibreOffice Calc that requires the binary entropy function. The function itself looks like H2(x) = − xlog2(x) − (1 − x)log2(1 − x), where 0log2(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: <email@example.com>