Calc2Code Tips of the Day

Day 2 Part 2

We are often asked if Calc2Code generated functions are as good as those written by hand. Our response is that they are at least as good. One reason is that it's much easier to write bullet-proof code with Calc2Code. This example will show you how to handle missing inputs, how to trap errors, and how to return information when there are problems with inputs.

And all of these require very little work. Take a look at quadraticformula.xls.

Quadratic formula? You mean that thing we learned as teenagers, adding to the angst of an already awkward era in our adolescence? Yes.

For those who have deleted that algebra lesson from their memories, the quadratic formula tells us what x must be when:

a * x^2 + b * x + c = 0

This is also known as the root of the equation, and it's like using Solver to find the value of a cell that makes another cell equal to 0. The formula for x is

(-b +/- (b^2 - 4 * a * c))/(2 * a)

This is pretty easy to type into a cell (except for the +/- part). But the objective now is to trap errors and make the function more useful. We're going to do a few things for that.

First, the inputs will be from C3:C6. Notice we have an input to tell the formula whether to use the plus or the minus for the +/- part of the formula. In column D, to the right of each input, is a formula which checks to see if that input is blank. if it is, it assigns a default value to the input; otherwise it just copies the input into this column. This is a nice convenience to add to a function. but it does require that the argument to the function be a special type that can take on a numerical or a blank value (it so happens that type can also be a text value, which will prove helpful for the value of the outputof the function).

The part we will take the square root of is calculated separately in C8. This allows us to test for negative values in C9. If we are going to take a square root of a negative number, we catch it and set the value to the word "Imaginary." If you like, you can extend this example to take the absolute value of C8, and return something like 4+3i instead when C8 is negative.

We also handle the cases where a is 0, which would lead to a divide by zero in the quadratic formula, but we can still solve for root of b * x + c = 0.

And we finally check to see if a and b are both 0, which means there is nothing to solve for, so we return a text message indicating that if appropriate.

So the final result in C13 might be a number, it it might be a text error message. To turn this into a function with Calc2Code the configuration would look like this:

Notice that the input and output data types are XLOPERS. These allow them to be text, numbers, missing, or errors like #VALUE.

While it does take a little bit of extra effort to employ these checks in the spreadsheet, it makes for better and safer calculations, and the methods presented here are simpler than doing equivalent bullet proofing in a language like C++.