STEYX Function

Returns the standard error of the predicted y values in a straight-line fit.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

Want to practice STEYX in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

STEYX measures how far the points typically sit from the best-fit line, in y's units. Small means the line predicts well; large means the points are scattered.

It's calculated as √(sum of squared residuals ÷ (n − 2)), and is often used to put a rough ± range around a prediction.

Syntax

=STEYX(known_y's, known_x's) Explain

Arguments

ArgumentRequired?What it does
known_y'sYesThe values you want to predict (for example, sales).
known_x'sYesThe values that drive them (for example, ad spend), the same size as known_y's.

Examples

Example 1 — How far off is the trend line?

Sales predicted from ad spend.

E2fx=STEYX(C2:C9,B2:B9)
ABCDE
1MonthAd spend ($k)Sales ($k)MeasureResult
2Jan221Std error1.0938
3Feb325Forecast @1053.29
4Mar533+ 2 errors55.47
5Apr428
6May638
7Jun740
8Jul846
9Aug531
=STEYX(C2:C9,B2:B9) Explain

Actual sales typically land within about ±$1.1k of the line. Two standard errors give a rough upper bound for a $10k month.

Common mistakes

  • #DIV/0! Fewer than three pairs of numbers, or all the x values are the same.
  • #N/A The ranges are different sizes.
  • A rough guide only. STEYX ignores the extra uncertainty of predicting far outside your x values.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.