Swapping the two coefficients
The return order reverses the predictor order. G2 is the coefficient of B, selected from column two; G3 is the coefficient of C, selected from column one.
Fit two predictors and inspect the residuals
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter separate formulas in G2:G8. Fit measured Y in D2:D9 against both X1 and X2 in B2:C9 with an intercept. G2 is the X1 coefficient, G3 the X2 coefficient and G4 the intercept. G5 estimates Y at B12:C12. G6 is training R-squared, G7 residual standard error with five degrees of freedom, and G8 the sum of squared differences between all eight measured and fitted responses. Keep computed values unrounded.
Edit an observation or the new input
Apply one numeric value to the actual worksheet. Each source row stays paired. The complete model is validated before an edit replaces a valid input.
Applied value:
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G2, G3, G4, G5, G6, G7, G8. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Estimate one response from two paired predictors, then reconcile the model’s coefficients, new estimate and fit diagnostics.
THE QUICK ANSWER01
Estimate one response from two paired predictors, then reconcile the model’s coefficients, new estimate and fit diagnostics.
G2
=INDEX(LINEST($D$2:$D$9,$B$2:$C$9,TRUE,FALSE),1,2)G3
=INDEX(LINEST($D$2:$D$9,$B$2:$C$9,TRUE,FALSE),1,1)G4
=INDEX(LINEST($D$2:$D$9,$B$2:$C$9,TRUE,FALSE),1,3)G5
=TREND($D$2:$D$9,$B$2:$C$9,$B$12:$C$12,TRUE)G6
=INDEX(LINEST($D$2:$D$9,$B$2:$C$9,TRUE,TRUE),3,1)G7
=INDEX(LINEST($D$2:$D$9,$B$2:$C$9,TRUE,TRUE),3,2)G8
=SUMXMY2($D$2:$D$9,TREND($D$2:$D$9,$B$2:$C$9,$B$2:$C$9,TRUE))For the example data
G2 1.9G3 -3.9G4 4.900000000000001G5 11.5G6 0.9407407407407407G7 1.3856406460551023G8 9.600000000000005
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Interpreting one coefficient
The X1 coefficient describes the fitted change per unit of X1 while X2 stays fixed. Reading it as a one-variable slope omits the second predictor. The intercept is the fitted value when both predictors are zero.
Training fit and a new estimate
The residual is measured Y less fitted Y. Its square contributes to SSE; the sample residual standard error uses five degrees of freedom because the model estimates two slopes and an intercept from eight observations. R-squared describes these training observations. New observations need their own accuracy checks.
Declared input model
Use eight complete numeric rows: X1 and X2 from −100 through 100, measured Y from −100,000 through 100,000, and each new predictor from −200 through 200. Each centered predictor sum of squares must be at least 0.1 and 1−correlation² at least 0.02. Y must vary. These boundaries keep a full-rank, reasonably conditioned two-predictor lesson; redundant-column handling is a separate model choice.
Read the full step-by-step walkthrough
- Keep the three source columns paired
Each row contains X1, X2 and one measured Y. Fit all eight observations together; sorting a predictor by itself would change the model.
- Read coefficients in their returned order
With X1 followed by X2 in B:C, LINEST returns X2’s coefficient first, then X1’s coefficient, then the intercept. INDEX selects the actual parameter needed for each labelled output.
- Retain a fitted intercept
TRUE allows a fitted response at X1=0 and X2=0. Forcing it to zero changes both slopes and the resulting estimate.
- Supply one complete new observation
B12:C12 contains the new X1 and X2 together. TREND uses the same measured response, both training predictors and the explicit intercept setting.
- Inspect fit in response units
G6 reports the share of centered response variation explained in this training source. G7 reports residual standard error: the square root of SSE divided by 8−2−1=5.
- Reconcile every training residual
TREND at B2:C9 returns eight fitted responses. SUMXMY2 squares and sums their differences from the corresponding measured responses. Zero residuals remain numeric zero.
Things to watch
Fitting only one predictor
Use B2:C9 as one known-X rectangle. Two separate one-variable slopes do not generally equal the joint coefficients.
Forcing an intercept or rounding early
Use TRUE and retain full precision. An intercept of zero and rounded coefficients describe different calculations.
About this example
Original bounded sources checked with real Univer formulas and independent exact-rational/high-precision targets. Native Excel/Sheets execution remains pending; training fit does not establish future predictive accuracy.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.