Substituting an additive trend
TREND fits an additive change in raw Y. This task requires a multiplicative curve fitted in log space.
Fit a per-unit multiplier and a new level
About this lesson
Fit a positive response across actual period gaps, then separate the multiplier, fractional change and log-space residual diagnostics.
Task requirements
Enter separate formulas in G2:G7. Fit positive Y in B2:B9 against the actual periods in A2:A9, allowing a fitted level at period zero. G2 is the per-unit multiplier and G3 the fitted level at zero. G4 is the estimate at D2, G5 the multiplier minus one, G6 log-space residual standard error with six degrees of freedom and G7 log-space training R-squared. Keep the period spacing and full computed precision.
Teaching · read-only worksheet ·
Step 1 / 6Use the actual period readings
More
Inspect the highlighted cells. This step has no automatic change to apply. Teaching is read-only; switch to Your turn to edit.
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
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. Browser calculations may differ from your Excel version.
Practice stays on this device. No worksheet data is uploaded.
THE QUICK ANSWER01
Fit a positive response across actual period gaps, then separate the multiplier, fractional change and log-space residual diagnostics.
G2
=INDEX(LOGEST($B$2:$B$9,$A$2:$A$9,TRUE,FALSE),1,1)G3
=INDEX(LOGEST($B$2:$B$9,$A$2:$A$9,TRUE,FALSE),1,2)G4
=GROWTH($B$2:$B$9,$A$2:$A$9,$D$2,TRUE)G5
=INDEX(LOGEST($B$2:$B$9,$A$2:$A$9,TRUE,FALSE),1,1)-1G6
=INDEX(LOGEST($B$2:$B$9,$A$2:$A$9,TRUE,TRUE),3,2)G7
=INDEX(LOGEST($B$2:$B$9,$A$2:$A$9,TRUE,TRUE),3,1)For the example data
G2 1.1980224898055702G3 40.15823816555429G4 170.40974172674171G5 0.19802248980557025G6 0.021163281304932138G7 0.9980437042323764
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
A multiplier applies per period unit
For a fitted curve y=b*m^x, m multiplies the level once per one-unit increase in x. A two-unit gap uses m squared. The source can contain irregular periods without pretending the row number is time.
Fit residuals before converting back
The least-squares fit minimizes squared residuals of log(Y). Exponentiating gives the level and multiplier. This fitted curve generally differs from fitting a straight line to raw Y; its log-space standard error is not an error bar in original units.
Declared input model
Use eight numeric period/positive-response pairs. Periods lie from −20 through 20, positive Y from 0.01 through 1,000,000, and the new period from −40 through 40. Period sum of squared deviations must be at least 0.1; log(Y) must vary. Fitted multiplier, scale and estimate must each lie between 0.00000001 and 100,000,000. Zero/negative responses cannot be logged in this lesson.
Read the full step-by-step walkthrough
- Use the actual period readings
A one-unit period change has one declared meaning. Periods may be negative, fractional, repeated or unevenly spaced as long as the source varies enough to identify a slope.
- Fit proportional changes
LOGEST fits a straight line to log(Y), then converts the slope and intercept back to a per-unit multiplier and a level at period zero. Every observed Y must be positive.
- Convert the multiplier to a fractional change
Subtract one from the multiplier. A multiplier of 0.8 corresponds to a −0.2 fractional change per period unit; it does not mean adding −0.2 original Y units.
- Use the new period explicitly
GROWTH uses the actual known periods rather than assuming consecutive row numbers. D2 is the new period; TRUE keeps a fitted level at zero.
- Keep diagnostics on the fitted log scale
The standard error and R-squared from LOGEST describe log(Y). The log residual standard error uses 8−1−1=6 degrees of freedom. Compare it with a log-space residual, not a raw Y difference.
- Review a period-zero estimate
When D2 is zero, the fitted new estimate equals the fitted level. Later or earlier periods extend the same fitted curve; their real accuracy needs new observations.
Things to watch
Using row numbers for irregular periods
Supply A2:A9 explicitly. Omitting known X changes unequal source spacing into consecutive positions.
Treating a multiplier as a rate
The fractional change is multiplier minus one. Keep the multiplier, fractional change and original-unit estimate in their own output cells.
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.