mag_cover

LET Function

Often, our Excel formulas are simple and short. As such, they are easy to read, understand and maintain over time. An example of such a formula is:

=SUM(A1)

But, as the complexity of our workbooks increases, so can the complexity of our formulas. Sometimes, we end up with unwieldy formulas that are long and  difficult to understand and maintain. An example of such a formula is:

=IF(VLOOKUP(B8,Table1,2,0)>100, VLOOKUP(B8,Table1,2,0), VLOOKUP(B8,Table1,2,0)*C8)

One cause for such lengthy formulas is duplication. That is, duplication within the formula. In this case, you can see that VLOOKUP(B8, Table1, 2, 0) is included three times.

As a general rule of thumb, eliminating duplication within formulas makes them easier to read and perhaps more important, maintain over time. For example, if we needed to update the VLOOKUP function to return the value in the third column instead of the second, we would need to replace the “2” with a “3” not just once, but three times. Although this may not take too long, the risk is that we will miss updating all instances and thus, risk unexpected or incorrect results.

Although there are many ways Excel users have tried to minimize formula duplication over the years, we have a great new option in the LET function. So, I’d like to talk through the LET function now.

Note: depending on your version of Excel, you may or may not have access to the LET function.

 

LET

The LET function essentially enables us to define a name and its corresponding value (or expression) and then reference the name as needed within the remainder of the formula. This name is defined and recognized within the LET function only, so, this is different than defining a name with the Name Manager. Names defined with the Name Manager are recognized throughout the workbook or worksheet. But, names defined with the LET function are available only within the function.

LET allows us to define more than 100 names in a single function if desired, but assuming we want to name just one, it would look like this:

=LET(name, name_value, calculation)

Where:

  • name is the name we define;
  • name_value is the corresponding value or expression; and
  • calculation is the expression that computes the value to return.

To demonstrate how the LET function works, we will start with a simple example before tackling the VLOOKUP formula above.

Assume we have a column of values as seen in Figure 1.

Figure 1

We want to write a formula that will place positive balances into the debit column. One option is to write a formula like this into D7:

=IF(C7>0, C7, 0)

We could fill the formula down and it would place positive values into the debit column, like Figure 2 shows.

Figure 2

We could use a similar approach for the credit column:

=IF(C7<0,-C7,0)

Fill it down, and bam: Figure 3.

Figure 3

Let’s review the formula we used for the debit column:

=IF(C7>0, C7, 0)

The C7 reference is duplicated. If we wanted to change the C7 reference, we would need to do it twice. We could eliminate this duplication by using the LET  function as follows:

=LET(bal, C7, IF(bal>0,bal,0))

If we wanted to change the C7 reference in this formula, we would only need to do it once. This makes our formula easier to update and maintain over time.

Now that we see the basic function syntax, we will apply the LET function to remove the VLOOKUP duplication from our first formula.

Illustration

Let’s take another look at our original VLOOKUP formula:

=IF(VLOOKUP(B8,Table1,2,0)>100, VLOOKUP(B8,Table1,2,0), VLOOKUP(B8,Table1,2,0)*C8)

The purpose of the formula is to compute payroll for the week. Some employees are hourly while some are on salary. The pay rates table, Table1, is shown in Figure 4.

Figure 4

You can see that the two hourly employees, DMK and CRT have their hourly pay rate displayed in the table, while the salaried employees have their weekly pay rate displayed.

Each week we collect their timesheets, which are used to track time spent on behalf of various clients for billing purposes. A summary of the total time per employee is shown in Figure 5.

Figure 5

It’s our job to write a formula that computes the Pay column values.

To determine the pay, our formula needs to display the value in the Rate column from Table1 for salaried employees, while it needs to apply the hourly pay rate to the number of hours for hourly employees.

One option is to assume that when the value in the Rate column is greater than 100, the employee is salaried. So, if the Rate in Table1 is greater than 100, we will simply retrieve it and display it in the Pay column. If not, our formula will assume the employee is hourly, and then retrieve the rate and multiply it by the number of hours to determine the pay.

If we wanted to start to convert this logic into a formula structure, we could think of it like this:

IF Rate>100, return Rate, otherwise return Rate*Hours

In fact, this is the logic used in our original formula, where VLOOKUP is used to retrieve the Rate from Table1:

=IF(VLOOKUP(B8,Table1,2,0)>100, VLOOKUP(B8,Table1,2,0), VLOOKUP(B8,Table1,2,0)*C8)

If we break this formula down, we can see the IF function has three arguments which reflect our logic:

  • VLOOKUP(B8,Table1,2,0)>100
    • Is the rate greater than 100?
  • VLOOKUP(B8,Table1,2,0)
    • If so, return the rate
  • VLOOKUP(B8,Table1,2,0)*C8
    • Otherwise, multiply the rate by hours

When we look at the formula like this, the duplication jumps right out. VLOOKUP(B8,Table1,2,0) appears three times. This means that if we wanted to change how we determine Rate, we would need to update the formula in three places.

We can eliminate this duplication with LET, like this:

=LET(Rate, VLOOKUP(B8,Table1,2,0), IF(Rate>100,Rate,Rate*C8))

The LET function has the following arguments:

  • Rate the name we are defining
  • VLOOKUP(B8,Table1,2,0) the value to be assigned to our name
  • IF(Rate>100,Rate,Rate*C8) the expression that computes the result

We write the formula in D8 and fill it down, and we get what you see in Figure 6.

Figure 6

In our updated formula, VLOOKUP appears only once. This means that if we want to change how we determine Rate, our formula only needs one update. This helps to simplify the maintenance of the formula over time and helps to reduce potential errors.

I hope that the LET function allows you to simply your formulas and minimize duplication. And remember, Excel rules!

Posted in

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

roadmap_title_multi