How to Convert a GLM in R to Excel

Your model is only useful once it is in production. This tutorial shows how to quickly convert a GLM to an Excel formula in 4 steps.

Step 1: Sep Up

Load the libraries. The tidyverse and broom packages have tools to make the data mapipulation easier. The library openxlsx creates Excel files.

library(tidyverse)
library(broom)
library(openxlsx)
excel = createWorkbook() #creates an Excel Workbook

Step 2: Fit a model

A linear model relates the target variable to the input variables with a mathematical formula. In this example, there are just two inputs, but the results generalize to any number of inputs.

\[g(Y) = \beta_0 + \beta_1 X_1 + \beta_2 X_2\]

Often a log transform is applied to the input variables. In this example, let’s take the log of $X_1$. The function g is the link function, and is commonly a log as well for regression problems.

\[log(Y) = \beta_0 + \beta_1 X_1 + \beta_2 log(X_2)\]

Fit a model and look at the coefficients. In this example, $\beta_0 = -1.83, \beta_1 = 0.053, \beta_2 = 1.37$. This means that the response Petal.Width is related to the inputs Sepal.Width and Petal.Length by the formula

\[log(Y) = -1.83 + 0.053 X_1 + 1.37 log(X_2)\]

glm <- glm(Petal.Width ~ Sepal.Width + log(Petal.Length), 
           family = gaussian(link = "log"),
           data = iris)
glm %>% tidy() %>% select(term, estimate)

Note that this is using the glm function which means that this same code will work for any combination of link function and response distribution. For example, if using logistic regression, just change the family to be binomial(link = "logit").

Step 3: Export

We could just hard-code these values into Excel, but because these coefficients are really random variables, they change when the data changes. Hard-coding values in this case would lead to a high-maintenance model. Each time that the data was changed we would need to go back and update these values!

Instead, add a worksheet to the Excel workbook to store the coefficients.

addWorksheet(excel, "coefficients")

Then, copy the values into this table.

glm %>% tidy() %>% select(term, estimate) %>% writeData(excel, "coefficients", .)

Do the same for the model’s predicted values.

addWorksheet(excel, "R predictions")
iris %>% 
  mutate(predicted_petal_width = predict(glm, iris, type = "response")) %>% 
  writeDataTable(excel, "R predictions", .)

Step 4: Verify the results

The Excel file that is still in R at this point. Save it as an .xlsx file, and then open the file in Excel and check the formula.

saveWorkbook(excel, "R GLM in Excel.xlsx", overwrite = T)

When you open the file in Excel you will see the model’s predictions.

Excel File

Now create a new column which will make these predictions in an Excel formula. In a new column, enter

=EXP(coefficients!$B$2+coefficients!$B$3*[@[Sepal.Width]])*[@[Petal.Length]]^coefficients!$B$4

As a check, these values should be identical to the predictions from the R model in the column predicted_petal_width.

If you are wondering why this formula looks different than the first one, than you are on the right track. There is a bit of algebra to make it simpler.

\[log(Y) = \beta_0 + \beta_1 X_1 + \beta_2 log(X_2) \rightarrow Y = exp( \beta_0 + \beta_1 X_1 + \beta_2 log(X_2)) = e^{\beta_0 + \beta_1 X_1} e^{log( X_2^{\beta_2})} = e^{\beta_0 + \beta_1 X_1} X_2^{\beta_2} \]

You can use this to simplify models which have a number of log-transformed inputs by taking the X to the power of Beta.

LS0tDQp0aXRsZTogIkhvdyB0byBDb252ZXJ0IGEgR0xNIGluIFIgdG8gRXhjZWwiDQpvdXRwdXQ6IGh0bWxfbm90ZWJvb2sNCi0tLQ0KDQpZb3VyIG1vZGVsIGlzIG9ubHkgdXNlZnVsIG9uY2UgaXQgaXMgaW4gcHJvZHVjdGlvbi4gIFRoaXMgdHV0b3JpYWwgc2hvd3MgaG93IHRvIHF1aWNrbHkgY29udmVydCBhIEdMTSB0byBhbiBFeGNlbCBmb3JtdWxhIGluIDQgc3RlcHMuDQoNCiMjIFN0ZXAgMTogU2VwIFVwDQoNCkxvYWQgdGhlIGxpYnJhcmllcy4gIFRoZSBgdGlkeXZlcnNlYCBhbmQgYGJyb29tYCBwYWNrYWdlcyBoYXZlIHRvb2xzIHRvIG1ha2UgdGhlIGRhdGEgbWFwaXB1bGF0aW9uIGVhc2llci4gIFRoZSBsaWJyYXJ5IGBvcGVueGxzeGAgY3JlYXRlcyBFeGNlbCBmaWxlcy4NCg0KYGBge3J9DQpsaWJyYXJ5KHRpZHl2ZXJzZSkNCmxpYnJhcnkoYnJvb20pDQpsaWJyYXJ5KG9wZW54bHN4KQ0KZXhjZWwgPSBjcmVhdGVXb3JrYm9vaygpICNjcmVhdGVzIGFuIEV4Y2VsIFdvcmtib29rDQpgYGANCg0KIyMgU3RlcCAyOiBGaXQgYSBtb2RlbA0KDQpBIGxpbmVhciBtb2RlbCByZWxhdGVzIHRoZSB0YXJnZXQgdmFyaWFibGUgdG8gdGhlIGlucHV0IHZhcmlhYmxlcyB3aXRoIGEgbWF0aGVtYXRpY2FsIGZvcm11bGEuICBJbiB0aGlzIGV4YW1wbGUsIHRoZXJlIGFyZSBqdXN0IHR3byBpbnB1dHMsIGJ1dCB0aGUgcmVzdWx0cyBnZW5lcmFsaXplIHRvIGFueSBudW1iZXIgb2YgaW5wdXRzLg0KDQokJGcoWSkgPSBcYmV0YV8wICsgXGJldGFfMSBYXzEgKyBcYmV0YV8yIFhfMiQkDQoNCk9mdGVuIGEgbG9nIHRyYW5zZm9ybSBpcyBhcHBsaWVkIHRvIHRoZSBpbnB1dCB2YXJpYWJsZXMuICBJbiB0aGlzIGV4YW1wbGUsIGxldCdzIHRha2UgdGhlIGxvZyBvZiAkWF8xJC4gIFRoZSBmdW5jdGlvbiBgZ2AgaXMgdGhlIGxpbmsgZnVuY3Rpb24sIGFuZCBpcyBjb21tb25seSBhIGxvZyBhcyB3ZWxsIGZvciByZWdyZXNzaW9uIHByb2JsZW1zLg0KDQokJGxvZyhZKSA9IFxiZXRhXzAgKyBcYmV0YV8xIFhfMSArIFxiZXRhXzIgbG9nKFhfMikkJA0KDQpGaXQgYSBtb2RlbCBhbmQgbG9vayBhdCB0aGUgY29lZmZpY2llbnRzLiAgSW4gdGhpcyBleGFtcGxlLCAkXGJldGFfMCA9IC0xLjgzLCBcYmV0YV8xID0gMC4wNTMsIFxiZXRhXzIgPSAxLjM3JC4gIFRoaXMgbWVhbnMgdGhhdCB0aGUgcmVzcG9uc2UgYFBldGFsLldpZHRoYCBpcyByZWxhdGVkIHRvIHRoZSBpbnB1dHMgYFNlcGFsLldpZHRoYCBhbmQgYFBldGFsLkxlbmd0aGAgYnkgdGhlIGZvcm11bGENCg0KJCRsb2coWSkgPSAtMS44MyArIDAuMDUzIFhfMSArIDEuMzcgbG9nKFhfMikkJA0KDQpgYGB7cn0NCmdsbSA8LSBnbG0oUGV0YWwuV2lkdGggfiBTZXBhbC5XaWR0aCArIGxvZyhQZXRhbC5MZW5ndGgpLCANCiAgICAgICAgICAgZmFtaWx5ID0gZ2F1c3NpYW4obGluayA9ICJsb2ciKSwNCiAgICAgICAgICAgZGF0YSA9IGlyaXMpDQpnbG0gJT4lIHRpZHkoKSAlPiUgc2VsZWN0KHRlcm0sIGVzdGltYXRlKQ0KYGBgDQoNCk5vdGUgdGhhdCB0aGlzIGlzIHVzaW5nIHRoZSBgZ2xtYCBmdW5jdGlvbiB3aGljaCBtZWFucyB0aGF0IHRoaXMgc2FtZSBjb2RlIHdpbGwgd29yayBmb3IgYW55IGNvbWJpbmF0aW9uIG9mIGxpbmsgZnVuY3Rpb24gYW5kIHJlc3BvbnNlIGRpc3RyaWJ1dGlvbi4gIEZvciBleGFtcGxlLCBpZiB1c2luZyBsb2dpc3RpYyByZWdyZXNzaW9uLCBqdXN0IGNoYW5nZSB0aGUgYGZhbWlseWAgdG8gYmUgYGJpbm9taWFsKGxpbmsgPSAibG9naXQiKWAuDQoNCiMjIFN0ZXAgMzogRXhwb3J0DQoNCldlIGNvdWxkIGp1c3QgaGFyZC1jb2RlIHRoZXNlIHZhbHVlcyBpbnRvIEV4Y2VsLCBidXQgYmVjYXVzZSB0aGVzZSBjb2VmZmljaWVudHMgYXJlIHJlYWxseSAqcmFuZG9tIHZhcmlhYmxlcyosIHRoZXkgY2hhbmdlIHdoZW4gdGhlIGRhdGEgY2hhbmdlcy4gIEhhcmQtY29kaW5nIHZhbHVlcyBpbiB0aGlzIGNhc2Ugd291bGQgbGVhZCB0byBhIGhpZ2gtbWFpbnRlbmFuY2UgbW9kZWwuICBFYWNoIHRpbWUgdGhhdCB0aGUgZGF0YSB3YXMgY2hhbmdlZCB3ZSB3b3VsZCBuZWVkIHRvIGdvIGJhY2sgYW5kIHVwZGF0ZSB0aGVzZSB2YWx1ZXMhDQoNCkluc3RlYWQsIGFkZCBhIHdvcmtzaGVldCB0byB0aGUgRXhjZWwgd29ya2Jvb2sgdG8gc3RvcmUgdGhlIGNvZWZmaWNpZW50cy4NCg0KYGBge3J9DQphZGRXb3Jrc2hlZXQoZXhjZWwsICJjb2VmZmljaWVudHMiKQ0KYGBgDQoNClRoZW4sIGNvcHkgdGhlIHZhbHVlcyBpbnRvIHRoaXMgdGFibGUuDQoNCmBgYHtyfQ0KZ2xtICU+JSB0aWR5KCkgJT4lIHNlbGVjdCh0ZXJtLCBlc3RpbWF0ZSkgJT4lIHdyaXRlRGF0YShleGNlbCwgImNvZWZmaWNpZW50cyIsIC4pDQpgYGANCg0KRG8gdGhlIHNhbWUgZm9yIHRoZSBtb2RlbCdzIHByZWRpY3RlZCB2YWx1ZXMuDQoNCmBgYHtyfQ0KYWRkV29ya3NoZWV0KGV4Y2VsLCAiUiBwcmVkaWN0aW9ucyIpDQppcmlzICU+JSANCiAgbXV0YXRlKHByZWRpY3RlZF9wZXRhbF93aWR0aCA9IHByZWRpY3QoZ2xtLCBpcmlzLCB0eXBlID0gInJlc3BvbnNlIikpICU+JSANCiAgd3JpdGVEYXRhVGFibGUoZXhjZWwsICJSIHByZWRpY3Rpb25zIiwgLikNCmBgYA0KDQoNCiMjIFN0ZXAgNDogVmVyaWZ5IHRoZSByZXN1bHRzDQoNClRoZSBFeGNlbCBmaWxlIHRoYXQgaXMgc3RpbGwgaW4gUiBhdCB0aGlzIHBvaW50LiAgU2F2ZSBpdCBhcyBhbiBgLnhsc3hgIGZpbGUsIGFuZCB0aGVuIG9wZW4gdGhlIGZpbGUgaW4gRXhjZWwgYW5kIGNoZWNrIHRoZSBmb3JtdWxhLg0KDQpgYGB7cn0NCnNhdmVXb3JrYm9vayhleGNlbCwgIlIgR0xNIGluIEV4Y2VsLnhsc3giLCBvdmVyd3JpdGUgPSBUKQ0KYGBgDQoNCldoZW4geW91IG9wZW4gdGhlIGZpbGUgaW4gRXhjZWwgeW91IHdpbGwgc2VlIHRoZSBtb2RlbCdzIHByZWRpY3Rpb25zLg0KDQohW0V4Y2VsIEZpbGVdKEV4Y2VsIDEuUE5HKQ0KDQpOb3cgY3JlYXRlIGEgbmV3IGNvbHVtbiB3aGljaCB3aWxsIG1ha2UgdGhlc2UgcHJlZGljdGlvbnMgaW4gYW4gRXhjZWwgZm9ybXVsYS4gIEluIGEgbmV3IGNvbHVtbiwgZW50ZXINCg0KDQpgPUVYUChjb2VmZmljaWVudHMhJEIkMitjb2VmZmljaWVudHMhJEIkMypbQFtTZXBhbC5XaWR0aF1dKSpbQFtQZXRhbC5MZW5ndGhdXV5jb2VmZmljaWVudHMhJEIkNGANCg0KDQpBcyBhIGNoZWNrLCB0aGVzZSB2YWx1ZXMgc2hvdWxkIGJlIGlkZW50aWNhbCB0byB0aGUgcHJlZGljdGlvbnMgZnJvbSB0aGUgUiBtb2RlbCBpbiB0aGUgY29sdW1uIGBwcmVkaWN0ZWRfcGV0YWxfd2lkdGhgLg0KDQpJZiB5b3UgYXJlIHdvbmRlcmluZyB3aHkgdGhpcyBmb3JtdWxhIGxvb2tzIGRpZmZlcmVudCB0aGFuIHRoZSBmaXJzdCBvbmUsIHRoYW4geW91IGFyZSBvbiB0aGUgcmlnaHQgdHJhY2suICBUaGVyZSBpcyBhIGJpdCBvZiBhbGdlYnJhIHRvIG1ha2UgaXQgc2ltcGxlci4NCg0KJCRsb2coWSkgPSBcYmV0YV8wICsgXGJldGFfMSBYXzEgKyBcYmV0YV8yIGxvZyhYXzIpIFxyaWdodGFycm93IFkgPSBleHAoIFxiZXRhXzAgKyBcYmV0YV8xIFhfMSArIFxiZXRhXzIgbG9nKFhfMikpID0gZV57XGJldGFfMCArIFxiZXRhXzEgWF8xfSBlXntsb2coIFhfMl57XGJldGFfMn0pfSA9ICBlXntcYmV0YV8wICsgXGJldGFfMSBYXzF9IFhfMl57XGJldGFfMn0gJCQNCg0KWW91IGNhbiB1c2UgdGhpcyB0byBzaW1wbGlmeSBtb2RlbHMgd2hpY2ggaGF2ZSBhIG51bWJlciBvZiBsb2ctdHJhbnNmb3JtZWQgaW5wdXRzIGJ5IHRha2luZyB0aGUgWCB0byB0aGUgcG93ZXIgb2YgQmV0YS4NCg0KDQo=