excel-lambda-GROWTHFROMOFFSET – Calculate % growth of current month over 12 months before

The gist for this lambda function can be found here.

I was straining my brain to understand the lambdas posted by user XLambda on the lambda forum at mrexcel.com.

This person is really mining the depths of what’s possible with lambda in Excel. I go there frequently when I want to understand how to do something.

While reading about ASCAN, I found myself watching this video by Leila Gharani which showed how to calculate year-to-date sums (YTD) using the SCAN function.

This got me thinking about a common calculation I’ve seen used in my work over the years

Calculate the % growth that this month represents over the same month of the prior year

If we have a value for March 2022, then the calculation is:

=[value from March 2022]/[value from March 2021]-1

This is easy enough. It’s also common enough to warrant a named lambda.

This is GROWTHFROMOFFSET:

=LAMBDA(month_col,value_col,month_offset,[if_error],
  IFERROR(
    LET(
      current_row,ROW()-MIN(ROW(month_col))+1,
      this_month,INDEX(month_col,current_row),
      this_value,INDEX(value_col,current_row),
      compare_month,EDATE(this_month,-1*month_offset),
      compare_value,SUMIF(month_col,compare_month,value_col),
      this_value/compare_value-1
    ),
    IF(ISOMITTED(if_error),NA(),if_error)
  )
)

It works like this:

This lambda does not spill.

Because of this, it can be used in a table as shown above, or it can be used against a range.

If using against a range, you must be sure to use absolute cell references in the first two parameters ($A$2:$A$37 instead of A1:A37).

GROWTHFROMOFFSET takes three required parameters:

1. month_col – this is the array or Table column or range of data (using absolute cell references) that holds the month for the current row
2. value_col – this is the array or Table column or range of data (using absolute cell references) that holds the value which you want to calculate growth for
3. month_offset – the number of months before the current month you want to calculate growth from. In the example above, I have calculated growth using 12 months before the current month

There is also an optional parameter

4. [if_error] – this is a value to show if the formula returns an error. This is usually where there is no data available for the calculation. For example, if the current row is Jan-2022 and the first available data is for Apr-2021, then 12 months before Jan-2022 is Jan-2021 and the formula cannot produce the calculation. In this case, we can pass a value to [if_error] to show instead of #N/A. If this parameter is not provided, the row will show #N/A

The Lambda is simple, but let’s break it down:

Finally, we perform the simple calculation this_value/compare_value-1 to calculate the output.

If that value returns an error, then we check if a value for [if_error] was provided. If it wasn’t, we return #N/A, otherwise we return [if_error].

And that’s that.

A practical lambda that will make sure each time we need to calculate this “growth” metric by months, it will always work the same way. There’ll be no mistakes by putting the numerator and denominator the wrong way round, or forgetting the -1, or accidentally selecting 11 months before the current month.

This is just one implementation of this function to calculate % growth of current month over 12 months before.

Hopefully it will inspire you to name the calculations you use regularly to reduce the possibility of error.

Share generously!