##### Page tree
Go to start of banner

# Constructing a Bayesian inference posterior distribution in Excel

It is a simple matter to go through the steps of estimating a single parameter using the Bayesian principles using Excel, providing the likelihood function is not too complicated.

Excel offers a number of statistical functions that make the task a great deal easier.

The method consists of the following steps:

1. Determine the parameter to be estimated, and write a column of values to test for this parameter;

2. In the next column calculate the prior density. For an uninformed prior this can often be simply a list of "1's, but may also be a function of the tested value. It might seem a pointless step to write a column of "1's when you have a flat prior, but we advise it anyway as it reinforces good habits;

3. In the third column calculate the probability of observing the data (the likely function) given the value of the parameter being tested in that row. If the likelihood function is complicated, try splitting it up into columns of partial calculations, which will also help you check you've got it right. Event trees are an excellent way to help you construct the probability of the pathways;

4. In the fourth column multiply the second and third column values for the same row to get the non-normalized posterior;

5. Use either Crystal Ball's Discrete distribution (for a discrete variable) or General distribution (for a continuous variable) to construct a normalized posterior distribution from the first and fourth columns (since both these functions automatically normalize the distribution). We very strongly recommend that you use an x-y scatter plot of the tested values against the un-normalized posterior column to adjust the range of values you have tested so that the posterior distribution is not shortened and to have a sufficient number of tested values within the highest confidence range of the posterior to give good detail. Plotting the prior and likelihood with the posterior, "normalized' so that they can be graphed together (we make them all have the same mean for this purpose) helps you understand and explain the relative contributions of the prior and likelihood.

A plot of the prior, likelihood and posterior is also very helpful to validate the model. For example, stressing the parameters of the likelihood function should produce a change of emphasis that you can predict. It also gives you a tool to analyze and explain the effect of accumulating extra data.

If the parameter is continuous (so you are using the General distribution) you can add as many values to test as you like as long as they are in ascending order, and they do not have to be equally spaced. If the parameter is discrete but the posterior covers a large number of possible values, you can test values at larger intervals than 1 and then use the General distribution imbedded in a ROUND(..,0) function to regain the discrete nature of the parameter. If you use the Discrete distribution , then every possible value must appear in the column of tested values, or values at equal increments if there are many, but you run the risk of an inaccurate analysis.

We provide many examples of the use of the construction method:

Identifying a weighted coin

A simple Bayesian inference example using the construction method

Tigers in the jungle

Simple construction model showing the interaction between likelihood functions and informed priors

Gender of a random sample of people

A simple construction model illustrating the importance of the prior distribution

A model to show how to incorporate hyperparameters by simulation, as well as offering both simulation and construction approaches to determining the posterior distribution

Bayesian estimation of a components mean time to failure MTTF

A simple construction example that shows how we use data that describe being above or below a threshold, instead of exact observations

• No labels