Lesson 8 · Excel: Zero to Expert
Excel Statistics & Forecasting Full Course: Quartiles, Regression, FORECAST.ETS | Excel 108
Sheet: Describe Workbook: excel108stats.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step…
- CourseExcel: Zero to Expert
- Lesson8 of 10
- Video1 h 13 min
- FormatExcel workbook · step-by-step guide
What you'll learn
Files for this lesson
Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.
- excel108_stats.xlsx63.4 KB
📝 Step-by-step guide
Excel 108: Statistics and Forecasting#
Sheet: Describe
Workbook: excel108_stats.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step with the video. Each section below lists exactly what to click and type.
Describing data: average, median and mode#
In A1 type
Staff salaries, Harbour Design StudioFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Staff|Salary| |---|---| |Aisha|62000| |Ben|58000| |Chloe|71000|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Dev|65000| |---|---| |Ella|60000| |Finn|58000| |Grace|67000| |Hamish|73000| |Isla|58000|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
Owner|240000| |---|---|Format B4:B13: number format $#,##0
Format A3:B3: bold, font colour White, Background 1, fill Blue, Accent 1
Starting in D3, type these rows (one value per cell, moving right):
|
Measure|All staff| |---|---|Starting in D4, type these rows (one value per cell, moving right):
|
Average|=AVERAGE(B4:B13)| |---|---|Starting in D5, type these rows (one value per cell, moving right):
|
Median|=MEDIAN(B4:B13)| |---|---|Starting in D6, type these rows (one value per cell, moving right):
|
Mode|=MODE.SNGL(B4:B13)| |---|---|Format E4:E6: number format $#,##0
Format D3:E3: bold, font colour White, Background 1, fill Blue, Accent 1
Look how different these are. The average is eighty one thousand two hundred dollars, the median sixty three thousand five hundred, and the mode fifty eight thousand. Nine of the ten people earn less than the average. The owner's salary drags it up, so it describes nobody in the room. The median is the middle. With ten values it's halfway between the two middle ones, sixty two and sixty five thousand, and it doesn't care how big the largest value is.
- In F3 type
Without owner - In F4 type the formula
=AVERAGE(B4:B12) - In F5 type the formula
=MEDIAN(B4:B12) - Format F4:F5: number format $#,##0
- Format F3: bold, font colour White, Background 1, fill Blue, Accent 1
- Format B3:F13: AutoFit columns
Without the owner, the average falls to about sixty three thousand five hundred and fifty six, a drop of more than seventeen thousand dollars. The median only moves from sixty three thousand five hundred to sixty two thousand. That's what robust means: one extreme value barely touches it.
- In B13 type
400000 - In B13 type
240000
Back to eighty one thousand two hundred, sixty three thousand five hundred, and fifty eight thousand.
When the mode misleads#
Measuring spread#
Sheet: Spread
In A1 type
Daily sales, two cafesFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Day|North|South| |---|---|---| |1|1180|800| |2|1220|1650| |3|1250|950|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
4|1190|1500| |---|---|---| |5|1210|1100| |6|1230|1400| |7|1200|700|
Starting in the next empty row, type these rows (one value per cell, moving right):
|
8|1240|1600| |---|---|---| |9|1170|1050| |10|1210|1350|Format B4:C13: number format $#,##0
Format A3:C3: bold, font colour White, Background 1, fill Green, Accent 6
Starting in E3, type these rows (one value per cell, moving right):
|
Measure|North|South| |---|---|---|Starting in E4, type these rows (one value per cell, moving right):
|
Average|=AVERAGE(B4:B13)| |---|---|Fill F4:G4 right (Ctrl+R)
Starting in E5, type these rows (one value per cell, moving right):
|
Minimum|=MIN(B4:B13)| |---|---|Starting in E6, type these rows (one value per cell, moving right):
|
Maximum|=MAX(B4:B13)| |---|---|
Starting in E7, type these rows (one value per cell, moving right):
|
Range|=F6-F5| |---|---|Select F5:G7
Press Ctrl+R
Both cafes average exactly twelve hundred and ten dollars a day. On that alone, they look identical. But North ranges from eleven seventy to twelve fifty, a range of just eighty dollars. South goes from seven hundred to sixteen fifty, a range of nine hundred and fifty.
Standard deviation: STDEV.S vs STDEV.P#
Starting in E8, type these rows (one value per cell, moving right):
|
STDEV.S|=STDEV.S(B4:B13)| |---|---|Starting in E9, type these rows (one value per cell, moving right):
|
STDEV.P|=STDEV.P(B4:B13)| |---|---|Starting in E10, type these rows (one value per cell, moving right):
|
VAR.S|=VAR.S(B4:B13)| |---|---|
- Select F8:G10
- Press Ctrl+R
- Format F4:G10: number format #,##0.00
- Format E3:G3: bold, font colour White, Background 1, fill Green, Accent 6
- Format E3:G10: AutoFit columns
North's standard deviation is about twenty five dollars eighty two, South's about three hundred and thirty six dollars forty nine. Same average, thirteen times the spread. STDEV.P is a little smaller, twenty four forty nine and three hundred and nineteen twenty two, because the sample version divides by n minus one instead of n. The variance is the standard deviation squared, in squared dollars: about six hundred and sixty seven for North, and one hundred and thirteen thousand two hundred and twenty two for South.
Starting in E11, type these rows (one value per cell, moving right):
|
Variation %|=F8/F4| |---|---|Fill F11:G11 right (Ctrl+R)
Format F11:G11: number format 0.0%
North varies by about two point one percent of its average, South by about twenty seven point eight percent. As a percentage, things of very different sizes become comparable.
Quartiles, percentiles and the IQR#
Sheet: Quartiles
- Paste: at A3, rows [['Order', 'Minutes'], [1001, 24], [1002, 31], [1003, 19], [1004, 27], [1005, 58], [1006, 22], [1007, 35], [1008, 29], [1009, 26], [1010, 33], [1011, 21], [1012, 28], [1013, 38], [1014, 25], [1015, 30], [1016, 66], [1017, 23], [1018, 27], [1019, 32], [1020, 20], [1021, 36], [1022, 26], [1023, 29], [1024, 34], [1025, 22], [1026, 30], [1027, 41], [1028, 28], [1029, 25], [1030, 31]]
- In A1 type
Delivery times (minutes) - Format A1: bold, font size 14
- Format A3:B3: bold, font colour White, Background 1, fill Orange, Accent 2
Starting in D3, type these rows (one value per cell, moving right):
|
Statistic|Minutes| |---|---|Starting in D4, type these rows (one value per cell, moving right):
|
Average|=AVERAGE(B4:B33)| |---|---|Starting in D5, type these rows (one value per cell, moving right):
|
Median|=MEDIAN(B4:B33)| |---|---|Starting in D6, type these rows (one value per cell, moving right):
|
Std deviation|=STDEV.S(B4:B33)| |---|---|Format E4:E6: number format 0.0
The average is thirty point five minutes, but the median is twenty eight point five. The two slow deliveries pull the average up by two minutes. And the standard deviation is about ten point one, inflated by the same two orders. This is exactly the situation where quartiles do a better job.
Starting in D8, type these rows (one value per cell, moving right):
|
Minimum (Q0)|=QUARTILE.INC(B4:B33,0)| |---|---|Starting in D9, type these rows (one value per cell, moving right):
|
Q1|=QUARTILE.INC(B4:B33,1)| |---|---|Starting in D10, type these rows (one value per cell, moving right):
|
Median (Q2)|=QUARTILE.INC(B4:B33,2)| |---|---|Starting in D11, type these rows (one value per cell, moving right):
|
Q3|=QUARTILE.INC(B4:B33,3)| |---|---|Starting in D12, type these rows (one value per cell, moving right):
|
Maximum (Q4)|=QUARTILE.INC(B4:B33,4)| |---|---|
The fastest delivery took nineteen minutes. Q1 is twenty five, the median twenty eight point five, Q3 thirty two point seven five, and the slowest sixty six. Q3 isn't a whole number because Excel interpolates between neighbouring values.
Starting in D13, type these rows (one value per cell, moving right):
|
IQR|=E11-E9| |---|---|Format E8:E13: number format 0.00
Format D3:E3: bold, font colour White, Background 1, fill Orange, Accent 2
Format D3:E13: AutoFit columns
The IQR is seven point seven five minutes. The middle half of all deliveries fall between twenty five and just under thirty three minutes, a more honest picture than an average plus or minus ten.
Percentiles#
Starting in G3, type these rows (one value per cell, moving right):
|
Percentile|Minutes| |---|---|Starting in G4, type these rows (one value per cell, moving right):
|
10th|=PERCENTILE.INC(B4:B33,0.1)| |---|---|Starting in G5, type these rows (one value per cell, moving right):
|
90th|=PERCENTILE.INC(B4:B33,0.9)| |---|---|Starting in G6, type these rows (one value per cell, moving right):
|
25th check|=PERCENTILE.INC(B4:B33,0.25)| |---|---|Format H4:H6: number format 0.0
Format G3:H3: bold, font colour White, Background 1, fill Orange, Accent 2
Format G3:H6: AutoFit columns
The tenth percentile is twenty one point nine minutes, and the ninetieth is thirty eight point three. So nine out of ten deliveries arrive within about thirty eight minutes. And the twenty fifth percentile is twenty five, the same as Q1.
- In G8 type
All quartiles - In G9 type the formula
=QUARTILE.INC(B4:B33,SEQUENCE(5,1,0)) - Format G9:G13: number format 0.00
One formula, five answers spilled down the column: nineteen, twenty five, twenty eight point five, thirty two point seven five and sixty six. That's the same five number summary as our panel, and it's exactly what a box and whisker chart draws, as we'll see shortly.
Ranking with RANK.EQ, LARGE and SMALL#
Sheet: Ranking
In A1 type
Quarterly sales by repFormat A1: bold, font size 14
Paste: at A3, rows [['Rep', 'Sales'], ['Ava', 48200], ['Ben', 39500], ['Chloe', 52100], ['Dev', 39500], ['Ella', 61300], ['Finn', 44800], ['Grace', 35700], ['Hamish', 57400], ['Isla', 41900], ['Jack', 46300]]
Format B4:B13: number format $#,##0
Starting in C3, type these rows (one value per cell, moving right):
|
Rank|Rank low first| |---|---|
- In C4 type the formula
=RANK.EQ(B4,$B$4:$B$13) - Fill C4:C13 down (drag the fill handle, or select it and press Ctrl+D)
- In D4 type the formula
=RANK.EQ(B4,$B$4:$B$13,1) - Fill D4:D13 down (drag the fill handle, or select it and press Ctrl+D)
- Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1
- Format B3:D13: AutoFit columns
Ella is ranked one, Hamish two, Chloe three. Ben and Dev are tied, so both get rank eight, and nobody gets nine: Grace is ten. Ties share the best rank and the next rank is skipped. In the low first column, Grace is one, and Ben and Dev share two.
Starting in F3, type these rows (one value per cell, moving right):
|
Top 3|Sales|Rep| |---|---|---|In G4 type the formula
=LARGE(B4:B13,SEQUENCE(3))In H4 type the formula
=XLOOKUP(G4#,B4:B13,A4:A13)Format G4:G6: number format $#,##0
Sixty one thousand three hundred for Ella, fifty seven thousand four hundred for Hamish, and fifty two thousand one hundred for Chloe. A live top three that updates if any sales figure changes.
Starting in F8, type these rows (one value per cell, moving right):
|
Bottom 3|Sales|Rep| |---|---|---|In G9 type the formula
=SMALL(B4:B13,SEQUENCE(3))In H9 type the formula
=XLOOKUP(G9#,B4:B13,A4:A13)Format G9:G11: number format $#,##0
Format F3:H3,F8:H8: bold, font colour White, Background 1, fill Blue, Accent 1
SMALL gives thirty five thousand seven hundred, then thirty nine thousand five hundred twice, because of the tie. But look at the names: Grace, Ben, and Ben again. Dev has disappeared! XLOOKUP always returns the first match, so both thirty nine five hundreds found Ben. This is a real trap whenever values can tie. The fix is to sort whole rows instead of looking values up.
- In J8 type
Bottom 3 (tie safe) - In J9 type the formula
=TAKE(SORTBY(A4:B13,B4:B13,1),3) - Format K9:K11: number format $#,##0
- Format J8: bold
- Format F3:K11: AutoFit columns
Now we get Grace, and then both Ben and Dev, each with their own row. Because the name and the sales stay together in the same row, a tie can't swap or lose anyone.
Distributions with COUNTIFS and FREQUENCY#
Sheet: Distribution
Paste: at A3, rows [['Order', 'Minutes'], [1001, 24], [1002, 31], [1003, 19], [1004, 27], [1005, 58], [1006, 22], [1007, 35], [1008, 29], [1009, 26], [1010, 33], [1011, 21], [1012, 28], [1013, 38], [1014, 25], [1015, 30], [1016, 66], [1017, 23], [1018, 27], [1019, 32], [1020, 20], [1021, 36], [1022, 26], [1023, 29], [1024, 34], [1025, 22], [1026, 30], [1027, 41], [1028, 28], [1029, 25], [1030, 31]]
Format A3:B3: bold, font colour White, Background 1, fill Orange, Accent 2
Starting in D3, type these rows (one value per cell, moving right):
|
Over|Up to|COUNTIFS|FREQUENCY| |---|---|---|---| |0|20| | | |20|25| | | |25|30| | |
Starting in the next empty row, type these rows (one value per cell, moving right):
|
30|35| |---|---| |35|40| |40|100|
- In F4 type the formula
=COUNTIFS($B$4:$B$33,">"&D4,$B$4:$B$33,"<="&E4) - Fill F4:F9 down (drag the fill handle, or select it and press Ctrl+D)
- In F10 type the formula
=SUM(F4:F9)
Two deliveries took twenty minutes or less, seven took over twenty and up to twenty five, ten fell in the busiest bin, over twenty five up to thirty, six in the next, two in the thirty five to forty bin, and three took over forty minutes. And the total is thirty, so every delivery landed in exactly one bin. Always do that check.
- In G4 type the formula
=FREQUENCY(B4:B33,E4:E8) - In G10 type the formula
=SUM(G4#) - Format D3:G3: bold, font colour White, Background 1, fill Orange, Accent 2
- Format D3:G10: AutoFit columns
FREQUENCY spilled six numbers from five bins: two, seven, ten, six, two and three, exactly like COUNTIFS. The sixth value is the overflow bin, for everything above the last limit. That's why I left out the hundred.
Histogram and box and whisker charts#
Sheet: StatCharts
- Paste: at A3, rows [['Minutes'], [24], [31], [19], [27], [58], [22], [35], [29], [26], [33], [21], [28], [38], [25], [30], [66], [23], [27], [32], [20], [36], [26], [29], [34], [22], [30], [41], [28], [25], [31]]
- Format A3: bold, font colour White, Background 1, fill Orange, Accent 2
- Select A3:A33 and insert a histogram chart titled “Delivery times (minutes)”
Excel chose the bins for us. Most deliveries pile up in the twenties and early thirties, with a long thin tail to the right. That's called right skew, and it's why the average was bigger than the median. To set your own bins, right click the horizontal axis, choose Format Axis, and type a bin width such as five.
- Select A3:A33 and insert a boxwhisker chart titled “Delivery spread”
The box covers the middle half, roughly twenty five to thirty three minutes. The line inside is the median, twenty eight and a half, and the cross is the average, thirty and a half. The lower whisker runs down to nineteen, the upper one stops at forty one, and two dots float above it, at fifty eight and sixty six. Excel has flagged those as outliers.
Finding outliers with the 1.5 x IQR rule#
Sheet: Outliers
Paste: at A3, rows [['Order', 'Minutes'], [1001, 24], [1002, 31], [1003, 19], [1004, 27], [1005, 58], [1006, 22], [1007, 35], [1008, 29], [1009, 26], [1010, 33], [1011, 21], [1012, 28], [1013, 38], [1014, 25], [1015, 30], [1016, 66], [1017, 23], [1018, 27], [1019, 32], [1020, 20], [1021, 36], [1022, 26], [1023, 29], [1024, 34], [1025, 22], [1026, 30], [1027, 41], [1028, 28], [1029, 25], [1030, 31]]
In C3 type
FlagFormat A3:C3: bold, font colour White, Background 1, fill Orange, Accent 2
Starting in E3, type these rows (one value per cell, moving right):
|
Fence|Minutes| |---|---|Starting in E4, type these rows (one value per cell, moving right):
|
Q1|=QUARTILE.INC(B4:B33,1)| |---|---|Starting in E5, type these rows (one value per cell, moving right):
|
Q3|=QUARTILE.INC(B4:B33,3)| |---|---|
Starting in E6, type these rows (one value per cell, moving right):
|
IQR|=F5-F4| |---|---|Starting in E7, type these rows (one value per cell, moving right):
|
Lower fence|=F4-1.5*F6| |---|---|Starting in E8, type these rows (one value per cell, moving right):
|
Upper fence|=F5+1.5*F6| |---|---|
- Format F4:F8: number format 0.000
- Format E3:F3: bold, font colour White, Background 1, fill Orange, Accent 2
- Format E3:F8: AutoFit columns
Q1 is twenty five, Q3 is thirty two point seven five, and the IQR is seven point seven five. One and a half IQRs is eleven point six two five minutes. So the lower fence is thirteen point three seven five, and the upper fence is forty four point three seven five. Any delivery faster than about thirteen minutes, or slower than about forty four, is an outlier.
In C4 type the formula
=IF(OR(B4<$F$7,B4>$F$8),"Outlier","")Fill C4:C33 down (drag the fill handle, or select it and press Ctrl+D)
Starting in E10, type these rows (one value per cell, moving right):
|
Outliers found|=COUNTIF(C4:C33,"Outlier")| |---|---|
Two outliers: order ten oh five at fifty eight minutes, and order ten sixteen at sixty six, the same two dots as the box and whisker chart. Forty one minutes is slow, but inside the fence.
- Conditional formatting on A4:C33: highlight formula
- Click E12
Rows eight and nineteen are now highlighted in red across all three columns, so your eye goes straight to the problem deliveries.
Starting in E11, type these rows (one value per cell, moving right):
|
Average, all|=AVERAGE(B4:B33)| |---|---|Starting in E12, type these rows (one value per cell, moving right):
|
Average, no outliers|=AVERAGEIFS(B4:B33,B4:B33,">="&F7,B4:B33,"<="&F8)| |---|---|Format F10:F12: number format 0.00
Format E10:E12: bold
Format E3:F12: AutoFit columns
With everything included, the average is thirty point five three minutes. Without the two outliers, it's twenty eight point two nine. Two orders out of thirty shifted the average by more than two minutes.
Correlation and scatter charts#
Sheet: Correlation
- Paste: at A3, rows [['Day', 'Max temp', 'Iced drinks'], [1, 22, 126], [2, 25, 135], [3, 28, 171], [4, 31, 178], [5, 19, 104], [6, 34, 195], [7, 27, 140], [8, 24, 142], [9, 36, 224], [10, 30, 183], [11, 21, 105], [12, 33, 206]]
- In A1 type
Beach kiosk: temperature and iced drinks - Format A1: bold, font size 14
- Format A3:C3: bold, font colour White, Background 1, fill Blue, Accent 1
- Format B3:C15: AutoFit columns
Starting in E3, type these rows (one value per cell, moving right):
|
Correlation|=CORREL(B4:B15,C4:C15)| |---|---|Format F3: number format 0.000
Format E3: bold
Zero point nine seven six. That's a very strong positive correlation: hotter days go with more iced drinks, almost in a straight line. With CORREL, the order of the two ranges doesn't matter. Swap them and you get the same answer.
- Select B3:C15 and insert a scatter chart titled “Temperature vs iced drinks”
Each dot is one day. Temperature runs along the bottom, drinks up the side, and the dots climb from bottom left to top right in a tight band. For a scatter chart, put the cause, or the thing you know first, in the left column. Excel puts the first column on the horizontal axis.
Python: code co = ws.ChartObjects(ws.ChartObjects().Count) t = co.Chart.SeriesCollection(1).Trendlines().Add(-4132) t.DisplayEquation = True t.DisplayRSquared = True
Click N3
A straight line of best fit now runs through the dots. The chart shows its equation, y equals seven point zero five three five x, minus thirty four point eight eight eight, and R squared equals zero point nine five three two. Next, we'll calculate those numbers with formulas.
Correlation is not causation#
Building the line: SLOPE, INTERCEPT and RSQ#
Sheet: Regression
Paste: at A3, rows [['Day', 'Max temp', 'Iced drinks'], [1, 22, 126], [2, 25, 135], [3, 28, 171], [4, 31, 178], [5, 19, 104], [6, 34, 195], [7, 27, 140], [8, 24, 142], [9, 36, 224], [10, 30, 183], [11, 21, 105], [12, 33, 206]]
Starting in D3, type these rows (one value per cell, moving right):
|
Predicted|Residual| |---|---|Format A3:E3: bold, font colour White, Background 1, fill Blue, Accent 1
Starting in G3, type these rows (one value per cell, moving right):
|
Slope (m)|=SLOPE(C4:C15,B4:B15)| |---|---|Starting in G4, type these rows (one value per cell, moving right):
|
Intercept (b)|=INTERCEPT(C4:C15,B4:B15)| |---|---|Starting in G5, type these rows (one value per cell, moving right):
|
R squared|=RSQ(C4:C15,B4:B15)| |---|---|
The slope is about seven point zero five: each extra degree goes with about seven more drinks. The intercept is about minus thirty four point nine, the line's value at zero degrees. Our data only covers nineteen to thirty six degrees, so that's just where the line crosses the axis, not a real prediction. And R squared is about zero point nine five.
Starting in G6, type these rows (one value per cell, moving right):
|
CORREL squared|=CORREL(B4:B15,C4:C15)^2| |---|---|Format H3:H6: number format 0.0000
Format G3:G6: bold
Seven point zero five three five, minus thirty four point eight eight eight four, and an R squared of zero point nine five three two, which is exactly the correlation squared. For a simple straight line, that's always true. R squared means: about ninety five percent of the day to day variation in drink sales is explained by temperature. The other five percent is everything else, like weekends, school holidays or a cruise ship in town.
- In D4 type the formula
=$H$3*B4+$H$4 - Fill D4:D15 down (drag the fill handle, or select it and press Ctrl+D)
- In E4 type the formula
=C4-D4 - Fill E4:E15 down (drag the fill handle, or select it and press Ctrl+D)
- Format D4:E15: number format 0.0
- Format A3:H15: AutoFit columns
On day one the line predicts about one hundred and twenty point three drinks, and the kiosk sold one hundred and twenty six, a residual of about plus five point seven. The biggest miss is day seven, a residual of about minus fifteen point six. For a least squares line, the residuals always add up to zero.
Starting in G8, type these rows (one value per cell, moving right):
|
Wrong order|=SLOPE(B4:B15,C4:C15)| |---|---|Format H8: number format 0.0000
Format G8: bold
Zero point one three five one. A completely different answer, and no error message. That's the slope of temperature against drinks: about zero point one three five degrees per extra drink. It's not wrong maths, it's answering the wrong question. Remember: SLOPE, INTERCEPT, RSQ and the forecasting functions always want the known y values first, the thing you want to predict.
- Clear G8:H8 (Delete key)
Predicting with FORECAST.LINEAR and TREND#
Sheet: Forecast
Paste: at A3, rows [['Day', 'Max temp', 'Iced drinks'], [1, 22, 126], [2, 25, 135], [3, 28, 171], [4, 31, 178], [5, 19, 104], [6, 34, 195], [7, 27, 140], [8, 24, 142], [9, 36, 224], [10, 30, 183], [11, 21, 105], [12, 33, 206]]
Format A3:C3: bold, font colour White, Background 1, fill Blue, Accent 1
Starting in E3, type these rows (one value per cell, moving right):
|
Forecast temp|35| |---|---|Starting in E4, type these rows (one value per cell, moving right):
|
Drinks expected|=FORECAST.LINEAR(F3,C4:C15,B4:B15)| |---|---|Format F4: number format 0.0
About two hundred and twelve drinks. Precisely, two hundred and eleven point nine eight. That's the same as seven point zero five three five times thirty five, minus thirty four point eight eight eight. So the kiosk should stock up for a big day.
- In F3 type
24
One hundred and thirty four point four drinks. Change the input, and the prediction updates instantly. You've just built a tiny demand forecasting tool.
Starting in H3, type these rows (one value per cell, moving right):
|
Week ahead temp|Drinks (TREND)| |---|---|In H4 type
24
- In I4 type the formula
=TREND(C4:C15,B4:B15,H4:H6) - Format I4:I6: number format 0.0
- Format E3:E4,H3:I3: bold
- Format E3:I6: AutoFit columns
TREND spilled three predictions: one hundred and thirty four point four, one hundred and seventy six point seven, and two hundred and thirty three point one. The first one matches FORECAST.LINEAR for twenty four degrees exactly, because it's the same line.
The danger of extrapolation#
Moving averages#
Sheet: Moving
- In A1 type
Online orders, Hobart bookshop - Format A1: bold, font size 14
- Paste: at A3, rows [['Month', 'Orders', '3-month avg'], ['Jan', 412], ['Feb', 388], ['Mar', 455], ['Apr', 430], ['May', 476], ['Jun', 462], ['Jul', 498], ['Aug', 471], ['Sep', 520], ['Oct', 505], ['Nov', 548], ['Dec', 603]]
- Format A3:C3: bold, font colour White, Background 1, fill Green, Accent 6
- In C6 type the formula
=AVERAGE(B4:B6) - Fill C6:C15 down (drag the fill handle, or select it and press Ctrl+D)
- Format C6:C15: number format 0.0
- Format B3:C15: AutoFit columns
The moving average starts at four hundred and eighteen point three for March and climbs smoothly to five hundred and fifty two in December. Click C15 and the formula bar shows the window: B13 to B15.
- Select A3:C15 and insert a line chart titled “Orders and 3-month moving average”
The orders line zigzags, while the moving average is smooth and shows steady growth. But it lags. In December orders jumped to six hundred and three, while the moving average is only five hundred and fifty two.
Seasonality and FORECAST.ETS#
Sheet: Seasonal
- Paste: at A3, rows [['Month', 'Sales'], ['1-Jan-2023', 38830], ['1-Feb-2023', 36670], ['1-Mar-2023', 31210], ['1-Apr-2023', 23940], ['1-May-2023', 18830], ['1-Jun-2023', 15510], ['1-Jul-2023', 15620], ['1-Aug-2023', 17580], ['1-Sep-2023', 21990], ['1-Oct-2023', 27020], ['1-Nov-2023', 33510], ['1-Dec-2023', 39870], ['1-Jan-2024', 41680], ['1-Feb-2024', 38910], ['1-Mar-2024', 33110], ['1-Apr-2024', 25680], ['1-May-2024', 20440], ['1-Jun-2024', 18100], ['1-Jul-2024', 16730], ['1-Aug-2024', 18690], ['1-Sep-2024', 22860], ['1-Oct-2024', 30310], ['1-Nov-2024', 34880], ['1-Dec-2024', 42190], ['1-Jan-2025', 42450], ['1-Feb-2025', 40270], ['1-Mar-2025', 34980], ['1-Apr-2025', 28650], ['1-May-2025', 22070], ['1-Jun-2025', 19460], ['1-Jul-2025', 17530], ['1-Aug-2025', 20280], ['1-Sep-2025', 25170], ['1-Oct-2025', 31960], ['1-Nov-2025', 39900], ['1-Dec-2025', 45200]]
- Format A4:A39: number format mmm yyyy
- Format B4:B39: number format $#,##0
- In C3 type
Month no - In C4 type the formula
=MONTH(A4) - Fill C4:C39 down (drag the fill handle, or select it and press Ctrl+D)
- Format A3:C3: bold, font colour Black, Text 1, fill Gold, Accent 4
- Select A3:B39 and insert a line chart titled “Gelato sales by month”
Three clear waves. Every summer, sales peak around forty thousand dollars or more. Every winter, they drop to around fifteen to twenty thousand. And each wave is a little higher than the one before: the business is growing as well. That's the classic combination: a trend plus a season.
Starting in E3, type these rows (one value per cell, moving right):
|
Month|Index| |---|---|In E4 type the formula
=SEQUENCE(12)In F4 type the formula
=AVERAGEIFS(B4:B39,C4:C39,E4#)/AVERAGE(B4:B39)Format F4:F15: number format 0.00
Format E3:F3: bold, font colour Black, Text 1, fill Gold, Accent 4
An index of one is an average month. January is one point four three, forty three percent above average, and December is highest at one point four eight. July is lowest at zero point five eight, about forty two percent below average. That's seasonality, measured.
Starting in H3, type these rows (one value per cell, moving right):
|
Forecast for|1-Jan-2026| |---|---|Format I3: number format mmm yyyy
Starting in H4, type these rows (one value per cell, moving right):
|
Straight line|=FORECAST.LINEAR(I3,B4:B39,A4:A39)| |---|---|Starting in H5, type these rows (one value per cell, moving right):
|
Line x month index|=I4*XLOOKUP(MONTH(I3),E4#,F4#)| |---|---|Format I4:I5: number format $#,##0
The straight line says about thirty one thousand and thirty eight dollars. But that's roughly an average month, and January is peak summer. The line has no idea about seasons. Multiply by January's index, and we get about forty four thousand three hundred and seventy three dollars, which looks far more believable next to the last three Januarys.
What FORECAST.ETS does#
Starting in H6, type these rows (one value per cell, moving right):
|
FORECAST.ETS|=FORECAST.ETS(I3,B4:B39,A4:A39)| |---|---|Format I6: number format $#,##0
Format H3:H6: bold
Format H3:I6: AutoFit columns
Compare the three numbers. The straight line sits at an average month. The ETS forecast, like our seasonal adjustment, lands up in summer territory, in line with the January peaks on the chart. It learned the season and the growth by itself. Treat its figure as an estimate, not an exact answer.
- In I3 type
1-Jul-2026
The straight line barely moves, up to about thirty one thousand eight hundred, because it only knows the trend. The seasonal adjustment uses July's index and drops to about eighteen thousand four hundred and forty two dollars, and the ETS forecast falls into winter territory too.
Project: Cafe sales analysis#
Sheet: Sales
- Paste: at A3, rows [['Month', 'No', 'Avg max temp', 'Sales'], ['1-Jan-2025', 1, 26.8, 45110], ['1-Feb-2025', 2, 27.1, 46440], ['1-Mar-2025', 3, 25.6, 46290], ['1-Apr-2025', 4, 23.2, 42220], ['1-May-2025', 5, 20.4, 40000], ['1-Jun-2025', 6, 17.9, 37220], ['1-Jul-2025', 7, 17.3, 37600], ['1-Aug-2025', 8, 18.6, 40350], ['1-Sep-2025', 9, 21.0, 42440], ['1-Oct-2025', 10, 22.7, 45410], ['1-Nov-2025', 11, 24.3, 46210], ['1-Dec-2025', 12, 25.9, 50430], ['1-Jan-2026', 13, 27.4, 50550], ['1-Feb-2026', 14, 26.9, 50490], ['1-Mar-2026', 15, 25.9, 50600], ['1-Apr-2026', 16, 22.8, 48530], ['1-May-2026', 17, 20.1, 45660], ['1-Jun-2026', 18, 18.2, 44500], ['1-Jul-2026', 19, 17.0, 43010], ['1-Aug-2026', 20, 18.9, 44510], ['1-Sep-2026', 21, 21.3, 47590], ['1-Oct-2026', 22, 23.1, 50740], ['1-Nov-2026', 23, 24.6, 51620], ['1-Dec-2026', 24, 26.5, 55610]]
- In A1 type
Manly cafe: monthly sales 2025 to 2026 - Format A1: bold, font size 14
Format A4:A27: number format mmm yyyy
Format C4:C27: number format 0.0
Format D4:D27: number format $#,##0
Starting in E3, type these rows (one value per cell, moving right):
|
Trend|Forecast| |---|---|Format A3:F3: bold, font colour White, Background 1, fill Blue, Accent 1
Click A4 and choose View → Freeze Panes
Starting in A28, type these rows (one value per cell, moving right):
|
1-Jan-2027|25| |---|---| |1-Feb-2027|26| |1-Mar-2027|27|Format A28:A30: number format mmm yyyy
Format A28:F30: fill #FFF2CC
Summary statistics panel#
Sheet: Summary
In A1 type
Manly cafe: sales summaryFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Measure|Value| |---|---|Starting in A4, type these rows (one value per cell, moving right):
|
Total sales|=SUM(Sales!D4:D27)| |---|---|Starting in A5, type these rows (one value per cell, moving right):
|
Average month|=AVERAGE(Sales!D4:D27)| |---|---|Starting in A6, type these rows (one value per cell, moving right):
|
Median month|=MEDIAN(Sales!D4:D27)| |---|---|
Starting in A7, type these rows (one value per cell, moving right):
|
Std deviation|=STDEV.S(Sales!D4:D27)| |---|---|Starting in A8, type these rows (one value per cell, moving right):
|
Lowest month|=MIN(Sales!D4:D27)| |---|---|Starting in A9, type these rows (one value per cell, moving right):
|
Highest month|=MAX(Sales!D4:D27)| |---|---|Starting in A10, type these rows (one value per cell, moving right):
|
Range|=B9-B8| |---|---|
- Format B4:B10: number format $#,##0
- Format A3:B3: bold, font colour White, Background 1, fill Blue, Accent 1
- Format A3:B10: AutoFit columns
Over two years the cafe took one million, one hundred and three thousand, one hundred and thirty dollars. The average month is about forty five thousand nine hundred and sixty four dollars, and the median forty five thousand nine hundred and thirty five. Almost identical, so no extreme months are pulling the average around. The standard deviation is about four thousand six hundred and thirty seven. The weakest month took thirty seven thousand two hundred and twenty, the best fifty five thousand six hundred and ten, a range of eighteen thousand three hundred and ninety.
Starting in A11, type these rows (one value per cell, moving right):
|
Variation %|=B7/B5| |---|---|Format B11: number format 0.0%
Starting in A13, type these rows (one value per cell, moving right):
|
2025 total|=SUMIFS(Sales!D4:D27,Sales!B4:B27,"<=12")| |---|---|Starting in A14, type these rows (one value per cell, moving right):
|
2026 total|=SUMIFS(Sales!D4:D27,Sales!B4:B27,">12")| |---|---|Starting in A15, type these rows (one value per cell, moving right):
|
Growth|=B14/B13-1| |---|---|Format B13:B14: number format $#,##0
Format B15: number format 0.0%
Sales vary by about ten point one percent around the average, a moderate amount for a cafe. In 2025 the cafe took five hundred and nineteen thousand seven hundred and twenty dollars. In 2026, five hundred and eighty three thousand four hundred and ten. That's growth of about twelve point three percent, a good year.
Best and worst months#
Starting in A17, type these rows (one value per cell, moving right):
|
Best month|=TEXT(XLOOKUP(B9,Sales!D4:D27,Sales!A4:A27),"mmmm yyyy")| |---|---|Starting in A18, type these rows (one value per cell, moving right):
|
Worst month|=TEXT(XLOOKUP(B8,Sales!D4:D27,Sales!A4:A27),"mmmm yyyy")| |---|---|Format A4:A18: bold
Format B17:B18: align right
AutoFit the width of column B:B (double-click its right edge)
The best month was December twenty twenty six, and the worst was June twenty twenty five. Summer at the top, winter at the bottom. That's our first hint that the weather matters.
Starting in D3, type these rows (one value per cell, moving right):
|
Top 3 months|Sales| |---|---|In E4 type the formula
=LARGE(Sales!D4:D27,SEQUENCE(3))In D4 type the formula
=TEXT(XLOOKUP(E4#,Sales!D4:D27,Sales!A4:A27),"mmm yyyy")Format E4:E6: number format $#,##0
Format D3:E3: bold, font colour White, Background 1, fill Blue, Accent 1
December twenty twenty six with fifty five thousand six hundred and ten, November twenty twenty six with fifty one thousand six hundred and twenty, and October twenty twenty six with fifty thousand seven hundred and forty. Every month is different here, so the XLOOKUP tie trap we saw earlier isn't a problem. With data that can tie, use SORTBY and TAKE instead.
Does the weather matter?#
Starting in D9, type these rows (one value per cell, moving right):
|
Relationships|Value| |---|---|Starting in D10, type these rows (one value per cell, moving right):
|
Correl with temp|=CORREL(Sales!C4:C27,Sales!D4:D27)| |---|---|Starting in D11, type these rows (one value per cell, moving right):
|
R squared (temp)|=RSQ(Sales!D4:D27,Sales!C4:C27)| |---|---|Starting in D12, type these rows (one value per cell, moving right):
|
Sales per degree|=SLOPE(Sales!D4:D27,Sales!C4:C27)| |---|---|Format E10:E11: number format 0.00
Format E12: number format $#,##0
The correlation is zero point seven four, a strong positive relationship. Warmer months are busier months. R squared is zero point five five, so temperature explains about fifty five percent of the month to month variation in sales. And each extra degree of average maximum temperature goes with about nine hundred and eighty five dollars more in monthly sales. That's a useful number for the owner when planning staff for summer.
Starting in D14, type these rows (one value per cell, moving right):
|
Trend per month|=SLOPE(Sales!D4:D27,Sales!B4:B27)| |---|---|Starting in D15, type these rows (one value per cell, moving right):
|
Intercept|=INTERCEPT(Sales!D4:D27,Sales!B4:B27)| |---|---|Starting in D16, type these rows (one value per cell, moving right):
|
R squared (time)|=RSQ(Sales!D4:D27,Sales!B4:B27)| |---|---|
- Format E14:E15: number format $#,##0.00
- Format E16: number format 0.00
- Format D9:E9: bold, font colour White, Background 1, fill Blue, Accent 1
- Format D4:D16: bold
- Format A3:E18: AutoFit columns
The trend is about three hundred and eighty dollars twelve per month: each month sells about three hundred and eighty dollars more than the one before. The intercept, the line's starting level, is about forty one thousand two hundred and twelve dollars. But R squared over time is only zero point three four. The growth explains about a third of the variation; most of the rest is the seasonal swing.
Trend line and forecast#
Sheet: Sales
- In E4 type the formula
=TREND(D4:D27,B4:B27,B4:B30) - Format E4:E30: number format $#,##0
One formula spilled twenty seven values, from E4 right down to E30. The line starts at about forty one thousand five hundred and ninety two dollars in January twenty twenty five, and climbs by three hundred and eighty dollars every month, to about fifty thousand three hundred and thirty five in December twenty twenty six. And it carries on into the yellow rows: about fifty thousand seven hundred and fifteen, fifty one thousand and ninety five, and fifty one thousand four hundred and seventy five.
- In F27 type the formula
=D27 - In F28 type the formula
=FORECAST.LINEAR(B28,$D$4:$D$27,$B$4:$B$27) - Fill F28:F30 down (drag the fill handle, or select it and press Ctrl+D)
- Format F27:F30: number format $#,##0
- Format B3:F30: AutoFit columns
January twenty twenty seven: fifty thousand seven hundred and fifteen dollars. February: fifty one thousand and ninety five. March: fifty one thousand four hundred and seventy five. Exactly the same as the TREND values beside them, as promised. Two functions, one line.
- Select A3:A30,D3:F30 and insert a line chart titled “Monthly sales: actual, trend and forecast”
The actual sales rise and fall with the seasons, but each year higher than the last. The trend line runs straight through the middle, and the forecast carries on from December's actual sales. Because the line ignores the seasons, the forecast sits below December's peak. January to March twenty twenty six were all about fifty and a half thousand, so a forecast of fifty thousand seven hundred to fifty one and a half thousand looks sensible.
Finishing the summary#
Sheet: Summary
Starting in A20, type these rows (one value per cell, moving right):
|
Forecast Jan-Mar 2027|=SUM(Sales!F28:F30)| |---|---|Starting in A21, type these rows (one value per cell, moving right):
|
Actual Jan-Mar 2026|=SUM(Sales!D16:D18)| |---|---|Starting in A22, type these rows (one value per cell, moving right):
|
Expected change|=B20/B21-1| |---|---|Format B20:B21: number format $#,##0
Format B22: number format 0.0%
Format A20:A22: bold
The forecast for January to March twenty twenty seven is one hundred and fifty three thousand two hundred and eighty six dollars, against one hundred and fifty one thousand six hundred and forty for the same months of twenty twenty six. That's about one point one percent more. Quite cautious next to twelve percent growth, because the straight line can't see the summer season.
- Conditional formatting on E4:E6: highlight data_bar
- Format A13:A15: bold
- In G3 type
Notes for the owner - In G4 type
Sales grew about 12% in 2026, around $380 more each month. - In G5 type
Warm months sell more: each extra degree is worth about $985 a month. - In G6 type
Forecast for Jan to Mar 2027 is about $153,000, a cautious estimate. - Format G3: bold, font size 12
- Click A1
Recap and what's next#
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



