Wow Google Sheets really has gotten much better than I remember. I’ve been trying to do some forecasting and curve fitting and discover quite a few functions that make it easier, but there doesn’t seem to be much of a guide, so here you go
Linear Regressions
First, decide what curve you want to fit, the first is the traditional linear regression, that is the curve is supposed to a straight line and the data that you have fits it. This is a question of figure out the constant slope and the intercept.
The Google function here is called FORECAST, this takes an array of X or independent variables, like say the date and an array of dependent variables, sort of the output, Y and gives you one point. This is very useful for a single point, like what will sales be like next year.
The other more useful function is called TREND, this takes the same array of X and Y, but you can also feed it an array of forecast variables, like say if you are doing sales forecasting, the X might be the years 2000 to 2018 and then you have below if the future years, then when you type this in it magically becomes an array formula.
What’s an Array Formula
This was one of the hardest things to understand in Excel, but the spreadsheet usually has a single cell output, that is you type the formula in and you get one result. That’s how FORECAST works, but one of the next ideas is that output could be an entire set of cells, that’s called an Array, with Excel you have some magic enter key that said that this formula and apply it over a range of sheets.
With Google Sheets, it doesn’t work that way, if you type in something like TREND and you expect five outputs (that is if in the input is five cells, then the output is five cells), it just sprays the numbers downward. Pretty weird stuff, but it works.
What if it is exponential growth?
Many business problems don’t grow linearly, for instance, sales actually grow by a percentage, so it is up and to the right. So they have a function for this as well called figuring out the fixed percentage change in each period that best matches the curve. Its a constant percentage increase model.
The function for this is called GROWTH and it works just like TREND. You basically feed it a range of cells that are the dependent variables, called data_y and then range of independent variables (with business, that is usually the fiscal year) called data_x and then you set a range of cells for the prediction called new_x
As an aside, you can see how it works because if you look at the percentage increase every year when you feed in new_x, it will always be a constant. That is what an exponential increase, which is why the function might also be called “constant growth”
