Trend lines in Tableau - Cell-by-Cell Formula Trace

Sample Data

Monthly sales data for five months

Cell	Value
A1	Month
B1	Sales
A2	Jan
B2	100
A3	Feb
B3	120
A4	Mar
B4	130
A5	Apr
B5	150
A6	May
B6	170

Formula Trace

y = a + b*x (linear trend line equation)

Step 1: Calculate mean of x (months as numbers 1 to 5)

Step 2: Calculate mean of y (sales)

Step 3: Calculate slope b = sum((x - mean_x)*(y - mean_y)) / sum((x - mean_x)^2)

Step 4: Calculate intercept a = mean_y - b*mean_x

Step 5: Trend line equation: y = 83 + 17*x

Step 6: Calculate predicted sales for March (x=3): y = 83 + 17*3

Cell Reference Map

    A      B
1 Month  Sales
2 Jan    100
3 Feb    120
4 Mar    130
5 Apr    150
6 May    170

Arrows: Sales (B2:B6) and Months (A2:A6) feed into trend line calculation

Months and sales values are used to calculate the trend line

Result

    A      B       C
1 Month  Sales  TrendLine
2 Jan    100    100
3 Feb    120    117
4 Mar    130    134
5 Apr    150    151
6 May    170    168

Column C shows predicted sales from the trend line equation for each month

Sheet Trace Quiz - 3 Questions

What does the slope (b) in the trend line equation represent?

A The average sales value

B The starting sales value at month 0

C The change in sales per month

D The total sales over all months

Key Result

Trend line fits data with y = intercept + slope * x, predicting y from x