How to Calculate AIC in Excel

Akaike’s Information Criterion, or AIC for short, is a commonly-used statistical measurement. In essence, it’s used to compare how statistical models compare in a qualitative standpoint. However, the AIC formula can be a little tricky to calculate. Fortunately, it’s much easier with spreadsheets. Let’s learn how to calculate AIC in Microsoft Excel.

How to Calculate AIC in Excel

Calculating AIC in Excel is quite simple. All you need are a few inputs, derived from the statistical analysis that you have performed on a model of data. In fact, there are only two critical inputs used in the AIC formula:

• K. This is the number of variables (called parameters) in your model, including the single intercept.
• Log-likelihood. This measures how well your model fits. Lower log-likelihood indicates a poor fit; higher log-likelihood implies a better fit.

The AIC formula itself reads as follows:

AIC = -2*Log-Likelihood + 2*K

Computing AIC in Excel, then, is quite straightforward. Excel doesn’t actually have a built-in AIC formula. But you can input the two variables (K and log-likelihood) into a pair of cells, and then construct a formula manually.

To do so, click into any empty cell in your workbook. In it, place your K value, the number of variables. In a second cell, place the log-likelihood that your statistical output derived. For this example, let’s use cells A1 and A2 for these two inputs.

Then, in a third cell, input the AIC formula:

=(-2*A2)+(2*A1)

When you’ve inputted your formula, hit Enter on your keyboard. Excel will return the result, in this case, 48.25. 

 

As you can see, it’s easy to calculate Akaike’s Information Criterion, or AIC, thanks to Microsoft Excel. This is another example of how spreadsheets make performing statistical calculations and analysis a breeze. You can craft and work with the formulas in seconds.