Google Sheet to fill in the inflation numbers?

Is it possible so that, in the year 1992, I put in a salary of \$38,000, and in the year 2023, I put in the salary of \$123,000.

Now in between 1992 and 2023, there are many rows, for the year 1993, 1994, ..., 2022.

Is there a way to have Google Sheet automatically "fill in" the numbers in between according to constant inflation? I know I can use a formula, but I am looking for some kind of drag a region and autofill method.

You can fill in the numbers by dragging a handle, provided that you already know how much the salary should increment each year.

Since discovering the increment involves a calculation, i.e., a formula, it would probably make sense to use a formula for the whole thing.

To fill a range of two columns with years and evenly incrementing numbers from the given start and end values, use a formula like this:

``````=let(
yearStart, 1992, yearEnd, 2023,
valueStart, 38000, valueEnd, 123000,
numYears, yearEnd - yearStart + 1,
increment, (valueEnd - valueStart) / (numYears - 1),
years, sequence(numYears, 1, yearStart),
format_, lambda(n, query({n}, "format Col1 '\$0.00' ", 0)),
series, map(years, lambda(y, valueStart + (y - yearStart) * increment)),
hstack(years, map(series, format_))
)
``````
• so complicated... I can use `(123000 / 38000.0) ** (1/(2023 - 1992.0)) - 1 => 0.03756902737047341` and so use 3.7569% as the factor to multiply each year for the increase (essentially multiply by `1.03756902737047341` but don't want to do it manually each time Commented Jun 30, 2023 at 18:08
• Why would it be too complicated to paste a formula in a cell? Commented Jul 1, 2023 at 0:39

Formula in `A2`

``````={A1+1,B1*(123000/38000)^(1/(2023-1992))}
``````

You could also use absolute references to the start/end salary/year instead of the values

``````={A1+1,B1*(\$B\$32/\$B\$1)^(1/(\$A\$32-\$A\$1))}
``````

ARRAYFORMULA

Similar to @doubleunary's answer I think this is an excellent candidate for a one-cell solution.

Values in ARRAYFORMULA

In this formula, the hardcoded values for `sy, sv, ey, ev` are in the formula itself, but they could also be replaced by references to cells containing those values.

``````=LET(sy,1992, sv,38000, ey,2023, ev,123000,
yrs,ey-sy, seq,SEQUENCE(1+yrs,1,0),
ARRAYFORMULA({seq+sy, ev/((ev/sv)^(1/(yrs)))^(yrs-seq)}))
``````

ARRAYFORMULA with Named Ranges

This next formula uses Named Ranges based on the following table:

Name Reference
sy `E2` Starting Year
sv `F2` Starting Value
ey `E3` Ending Year
ev `F3` Ending Value
``````=LET(seq,SEQUENCE(1+ey-sy,1,0), ARRAYFORMULA({seq+sy,
ev/((ev/sv)^(1/(ey-sy)))^(ey-sy-seq)}))
``````