The Takeaway: You should avoid leaving hard-coded values in final formulas. However, you can and should use hard-coded values in incremental steps during development. This way you’re focusing on one functionality at a time. Once you’ve confirmed that the approach works, Replace those hard-coded values with derived, calculated or referenced values where possible.

Last time we added the first condition in our error checking IFS() routine: checking for low temperatures.

Now, as we saw last time this check works, so we've created the structure of our first error checking routine. However, we’ve now hard-coded our temperature range and error message into the formula. This means that if we wanted to change the temperature range we’d have to modify the table, the value and the error message in each formula they appeared in. It’s good practice to avoid this and replace it with either a derived value or a reference to a constant defined in a central place.

Let’s start with the temperature check. Because we already named the list of valid temperatures in our lookup table, Temperatures, we can easily derive the minimum temperature from that named range:

= T < MIN(Temperatures)

How about the error message? If we left this as text in the formula, then whenever we wanted to change the text of the error message we’d have to open this formula and change it directly, possibly in multiple places. With a complex formula such as this, we want to avoid that, since it just increases the risk of unintentionally changing something else.

We’ll approach this problem by creating a table of error messages, associating each with a name:

This means we can use the names in our functions, and are free to play with the wording of each error message without having to modify the function over again. As a bonus, we’ve also derived these error messages from the table ranges too, so even our error messages will automatically update if the lookup table changes.

Let’s make these two changes as our next incremental step in the lambda function, and check our tests:

There should be no changes in our test results, and that’s the case here. Great!

Now we're ready to add the rest of our error checking code.

Until next time, thanks for reading!

– Brendan

p.s. Enjoy this message? Read more at the Hyland Quality Systems website.