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

  1. [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:

name definition
current_row ROW()-MIN(ROW(month_col))+1

The row where the formula is entered: ROW(), minus the minimum row from the list of rows for month_col: MIN(ROW(month_col))

If month_col is on rows 5:10, then ROW(month_col)={5;6;7;8;9;10} and MIN(ROW(month_col))=5.

If the formula is on row 7, then 7-5=2. This number represents the index position in the month_col, as we see below.

this_month INDEX(month_col,current_row)

Here we use INDEX to get the month from the current row.

this_value INDEX(value_col,current_row)

Similarly, we use INDEX to get the value from the current row.

compare_month EDATE(this_month,-1*month_offset)

We use the EDATE function to return the date that is month_offset months before this_month

compare_value SUMIF(month_col,compare_month,value_col)

We use the SUMIF function to sum value_col where month_col is equal to compare_month

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!

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>