0

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.

1

2 Answers 2

1

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_)) 
)

See How does fill-down work with text strings, numbers, dates and formulas?

2
  • 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 Jun 30, 2023 at 18:08
  • Why would it be too complicated to paste a formula in a cell? Jul 1, 2023 at 0:39
0

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)}))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.