Calc2Code Tips of the Day

Day 1 Part 3

So Calc2Code can help you add to Excel’s list of built-in functions to make up for Excel’s deficiencies. Sometimes, we want to simplify an Excel function. For example, Excel’s LINEST function is powerful, and calculates a lot of statistics related to multiple regression. But what if you just need one thing, like R squared? Calc2Code can be used here, as well.

Here’s an example in rsquared.xls of LINEST being called, and returning all statistics:

There are coefficients, standard errors, F-stats, and more, along with R-squared. The R-squared is in the first column and the third row of the results.

All we really want right now is the R-squared, perhaps because we want to create a table of R-squared values later on. So lower down we have a call to INDEX which calls into LINEST to get the range, and then returns the value in row 3 column 1 of the result, which is the R-squared. We want this cell to be the return value of our new Calc2Code generated function. We set up the configuration like this:

Run the builder, and end up with a function which simply returns R-squared.

Now we can use this in other spreadsheets when LINEST is more than we want, or we want to format the results a bit differently than Excel does.

Stay tuned for more tips tomorrow.