Skip to content

Instantly share code, notes, and snippets.

@ncalm
Created April 16, 2022 17:15
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save ncalm/d9b7f7fdd5f3a1aa552fd6ca407889cb to your computer and use it in GitHub Desktop.
This Excel lambda function calculates the growth that a current month's value represents when compared to an arbitrary number of months prior to the current month
/*
GROWTHFROMOFFSET
Calculates the growth that a current month represents when compared
to the same measure an arbitrary number of months ago
Inputs:
- month_col: a Table column, a cell range (using absolute references) or an array containing the months formatted as dates
- value_col : a Table column, a cell range (using absolute references) or an array containing the values
- month_offset : the number of months prior to the month on the current row that you want to compare with
- [if_error] : an optional value to return in case of error
Returns:
A decimal between 0 and 1 representing the calculation:
[current month value] / [value from offset months ago] - 1
More details here:
https://www.flexyourdata.com/blog/excel-lambda-growthfromoffset-calculate-growth-of-current-month-over-12-months-before/
*/
=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)
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment