Here are some useful functions you can use in Excel with BERT. If you’re not familiar with BERT or landed here via search engine, have a look at the front page for quick install and usage instructions.
These functions aren’t guaranteed to be efficient, correct, comprehensive, or necessarily useful. You are encouraged to modify them for your needs. Unless otherwise noted, all code on this page is in the public domain.
If you have a suggestion, comment, or request for a particular function, please let us know.
To use any of these functions, you just need to save the source file into your BERT startup folder. Right-click on one of the Save links below and select Save link as…
Save the file into
Documents\BERT\functions. You’ll see a note in the BERT console when a new file is loaded.
You can also copy the code below and paste it into your own file. any file in the BERT startup folder will be automatically reloaded when you save changes.
You can sort data with excel, but if that data changes it won’t re-sort. Also you lose the original order. Use this function to insert a sorted range that’s actively linked to the original range.
Shuffling data can be useful for bootstrap analysis or for randomizing fixed data sets (dice, cards).
Dimensionality reduction using Pricipcal Components Analysis (PCA)
Get a correlation matrix from multiple correlated data sets.
Two tests for normality in a data set: the Shaprio-Wilk and Kolmogorov-Smirnov tests. In each case, the exported functions return the p-value from the test.
Given a set of data, find lambda for the Box-Cox transform.
Functions for getting eigenvalues and eigenvectors from a matrix.
Generate call & put values using a trinomial lattice.
Generate call & put values using a binomial lattice.
Generate the binomial lattice price matrix in a spreadsheet.
This function is an example of using R graphics in Excel with the BERT graphics device. It generates a basic histogram of the input data.
maps package, it’s easy to create graphical maps from a list of countries (you could do the same thing for other geographic entities, like US states or French départments).
Before using this function, make sure to install the
maps package; use Packages > Install Packages from the BERT console menu.
Not a function in and of itself, but an example of how to use the Excel API to get a reference to the calling cell (the cell containing the function).