In my previous post I created a formula that calculates the month number for a financial year. That formula used the MONTH function four times. I thought I would share a technique you can use to define functions as a variable in LET function.
The image below shows the formula from my previous post.
You can check out the previous post at the link below.
Variables can be used to capture a value or a range. They can also be used to capture a function.
The formula below works in the same way.
=LET(fn,MONTH,fn($A6)-fn(B$2)-(fn($A6)<=fn(B$2))*-12)The fn variable captures the MONTH and then you can use the fn variable to replace all the MONTH functions in the formula.
I agree, using LET to handle repeated function names is really useful, especially for unnecessarily (in my opinion) long function names like SEQUENCE, TEXTBEFORE, REGEXEXTRACT, etc. However, sometimes looking deeper may be advantageous. For example, I would break down your original formula to this instead…
=LET(mn,MONTH($A6),fy,MONTH(B$2),mn-fy-(mn<=fy)*-12)
since the function name along with its argument repeat.
Note: I am not a financial and or accounting type person (I was a Civil Engineer during my working career), so I am not sure if there are abbreviations that would be more accurate and self-describing than the mn and fy that I used.
Thanks Rick – yes readability + self describing are important too.
How about.
=LET(fn,MONTH,mth,fn($A6),fymth,fn(B$2),mth-fymth-(mth<=fymth)*-12)