With many complex calculations, especially those involving one or more IF functions (also, the FILTER function and various lookup functions), the same expression will often be repeated multiple times in a formula. This can result in lengthy and hard-to-read formulas and can also cause performance issues, given that the same (often complex) expression can be calculated multiple times.
The LET function allows us to assign names to values and/or calculation results, with those names applying in the scope of a single LET function. Hence, using the LET function, we can write shorter and clearer formulas that will be easier to understand and modify and, at the same time, calculate faster.
The syntax of the LET function is as follows:
= LET ( name1 ; name_value1 ; [name2] ; [name_value2] ; … ; calculation )
At least one name/value pair must be defined, and up to 126 can be defined.
Consider this (impractical) example:
We have declared the value zero as x, the minimal value in column C as y, and we have then performed a calculation that used those two and one more value not defined by name. We did not have to input 0 and 1 directly into our AVERAGE function!
Now consider this very practical example:
Here, we have a sample of YTD financial data, budget, forecast, and actual. We need to calculate the MTD figures for all forecasting periods, including the first forecasting period (in this case, February). The value we are looking for is 1,75 MEUR, given that our YTD forecast for February is 3,25 MEUR and our actual value for January is 1,5 MEUR.
We will obviously accomplish this by checking IF there is any data for the forecast date; IF there is, we will use those values; IF there is not, use the actual figures. We will subtract the previous period using the same logic:
It’s a lengthy formula, and this is a simplified table.
We can clean up things a bit by using the LET function:
Now, instead of 6 times, we are writing our SUMIFS formula 4 times.
It is also much easier to read our IF formula and understand what is going on.
We can also define our date values using the first and the second name. We can use those values to define the names that follow afterwards:
Now, not only is our calculation easier to read and understand, but so are our variables!
Another, more precise approach would have been to find the first date for which the forecast data is available. IF the looked-for forecast date is available, THEN forecast figures can be used; ELSE the actual figures must be used.
We would then use only two SUMIFS functions from the start by having the MINIFS functions nested inside of the IF functions in order to define our scenarios. Not the simplest formula:
However, thanks to the LET function, this formula can also be comprehensive:
Dig deeper:
One thought on “Combining LET with IF”