How to do a regression or trendline calculation
Grid by Zaweba fits trend lines with LINEST, TREND and FORECAST, so you can find a slope, project the next values and check how well a straight line fits.
Fit the line with formulas. Grid by Zaweba has LINEST, which returns the slope and intercept of the best straight line through your data along with statistics about the fit, TREND, which returns values on that line, and FORECAST, which predicts a single new value. LOGEST does the same job for exponential growth.
Getting the data ready
Two columns of numbers: the known x values, such as months or advertising spend, and the known y values that depend on them, such as sales. They need to be the same length, with no text or blanks mixed in. A scatter chart of the two columns is a good first look, because a regression assumes a straight-line relationship and your eye will spot a curve quickly.
The quick answer with FORECAST
If all you want is the expected y for a new x, FORECAST is the simplest. Give it the new x, the known y values and the known x values, in that order, and it returns the prediction from the best-fit line. Next month's sales from the past year's figures is a typical use.
A column of predictions with TREND
TREND takes the known y and x values and returns the fitted value for each x. Point a new x argument at a list of future x values and it returns predictions for all of them. In Grid the results spill into the cells below, so one formula fills the whole column.
Plotting TREND's output alongside the actual figures on a line chart shows the trend clearly. Charts in Grid follow their data live, so if you add another month, the line updates.
The full picture with LINEST
LINEST returns the slope and the intercept: the line's equation. Wrapped in INDEX, you can pull out either one on its own. Ask for statistics as well and it returns a block of figures including the standard errors, the R squared value that says how much of the variation the line explains, and more. With dynamic arrays that block simply spills into place.
LINEST also handles several x columns at once, for a multiple regression where sales depend on both price and advertising, say. Give it a known x range with more than one column and it returns a coefficient for each.
For growth that compounds rather than adds, such as a population or an investment, LOGEST fits an exponential curve instead of a straight line, and GROWTH projects values on it.
Related statistical functions
Grid's Statistical family has 106 functions, and several pair well with a regression. SLOPE and INTERCEPT give the two line figures directly. CORREL and RSQ measure how closely two columns move together. STEYX gives the standard error of the predictions. Each is listed in Insert Function (fx) with an explanation of its arguments.
Reading the results sensibly
A regression always draws a line, even through data with no real pattern, so check R squared before trusting a forecast. A value near 1 means the line fits closely. A value near 0 means x tells you little about y.
Be careful with predictions far outside your data. A trend that held over twelve months tells you little about the next five years.
And look at the scatter chart. A few unusual points can pull the line a long way. If you've connected your own AI account, Analyse selection will list outliers with their cell addresses, but deciding whether to keep them is your call.
With these functions Grid by Zaweba gives you the same regression tools as other spreadsheets, following the same conventions, so results match when you share the workbook.
