24

## Formula 101 – #4 Relational Operators, Boolean Operators and Decision Functions

There are times that we want to display values based on certain decision making process. For example, we may want to show the size of the gap if the market gap up, and zero if that is not the case. This is a job for the relational operators, boolean operators and decision functions.

This tutorial is longer than the previous ones due to the fact that there are three (3) closely related concepts being introduced here. Take your time to read this through.

**What are relational operators?**

Relational operators are similar to the **arithmetic operators** that you have already learned in the first tutorial, which operate on two items at a time.

There are six (6) relational operators in total,

` > greater than comparison`

< less than comparison
>= greater than or equal to comparison

<= less than or equal to comparison

= equal to comparison

<> not equal to comparison

Unlike the arithmetic operators you have seen, these operators return only 2 possible values. They return 1 when the comparison evaluates to true and 0 when the comparison evaluates to false.

Here are some comparison examples in formula,

`Close > PrevClose`

```
```Open >= Close

Close > Open

```
High - Close < 0.1
High - Open > Close - Low
```

For the last two examples, we are actually comparing the calculated results, not just the 2 items next to the relational operator. The reason is that the relational operators have lower priority than the arithematic operators.

**What are boolean operators?**

Now, what if you want to specify more complex conditions, say, not just the close is greater than the open, but the close must be greater than the previous close too? We’ll need the boolean operators to connect multiple relations together.

There are 2 basic boolean operators.

The **and** operator returns 1 when both items its applied to are not zero, otherwise, it returns 0.

The **or** operator returns 1 when one of the two items it applied to is not zero. If both items it applied to are zero, then it returns 0.

Here is an example of using the boolean operators in combination with the relational operators,

`Last > Open and Last > PrevClose or Last < Open and Last < PrevClose`

Due to the fact that the *or* operator has a lower priority than the *and* operator, which in turn has a lower priority than the relational operators, the above formula will evaluate to 1 if the last price is greater than both open price and previous close, or, if the last price is less than both open price and previous close. For all other cases, it will return 0.

**What are decision functions?**

Decision functions are designed to utilize the results obtained from boolean expressions to help choosing calculation results among multiple choices.

By using decision functions we are no longer bounded to return values directly obtained from the calculations, instead, we are able to derive indirect answers based on relations we have identified to be more useful then the raw information.

**The if() function**

If function requires 3 parameters. Its general form is like the following,

`if (condition, true_result, false_result)`

The first parameter *condition* which can be any expression evaluated to 0 or 1, then based on this value, either the second or third parameter will be returned as the result of the *if* function.

For the second parameter *true_result* to be returned, the *condition* cannot return zero. That means, if the *condition* is a relation or a complex boolean expression, it is evaluated to true and returning the value 1.

For the third parameter *false_result* to be returned as the result of the *if* function, the *condition* must be evaluated to zero.

Sounds very confusing, right?

Lets take a look at a few examples to clarify the point.

Here is the first example,

`if (DayClose > PrevClose, DayClose, PrevClose)`

This formula is doing exactly the same thing as the MaxList function when used the following way,

`MaxList (DayClose, PrevClose)`

For both formulas above, we are trying to get the greater value between the current day close and the previous close.

Now lets look at another example,

`if (Open > PrevClose, (PrevClose + Open) / 2, 0)`

In this formula, we check if the current open is greater than the previous close (i.e. gap up), if so, we would like to know the midpoint between these 2 prices because it is a useful support price level.

Here is another example,

`if (Open > PrevClose, (PrevClose + Open) / 2, if (Last > PrevClose, PrevClose, 0))`

This is a more complex version of the previous example. After it is determined that the Open is not greater than the previous close, a nested if function is used to check if the last price is greater than the previous close, if so, we still want to show the previous close as our support price level.

**The choose() function**

The choose function is designed to assist users who is interested in making selection among multiple conditions and calculated results.

The general format of the choose function is like this,

`choose (condition1, result1, condition2, result2, ..., otherwise_result)`

What it means is that the first parameter *condition1* is evaluated, if it returns 1, then the result obtained from the second parameter *result1* is used as the result of the *choose* function.

If *condition1* returns 0, then the next condition will be evaluated. In this case, if *condition2* is evaluated to 1, then *result2* will be chosen as the result for the *choose* function.

This process continues until all condition parameters are checked through, then, the parameter *otherwise_result* will be used as the result of the *choose* function.

Here is the choose function version of the last example in the previous section,

`choose (Open > PrevClose, (PrevClose + Open) / 2, Last > PrevClose, PrevClose, 0)`

Looks more clear and easier to read.

We can even format it the following way to make it even easier to read,

`choosen (`

Open > PrevClose, (PrevClose + Open) / 2,

Last > PrevClose, PrevClose,

0)

Remember that there is no restriction as to how you format your formulas when you type them, so write it in a way that helps you read them easily is very important.

**Putting It All Together**

Here is a formula showing a very common usage of the choose function,

`choose (NetPct > 1.5, 3, NetPct > 1, 2, NetPct > 0, 1, NetPct > -0.5, 0, -1)`

If you put this formula into the quote window, you can then color code the column based on the scoring you have assigned to the symbol based on its Net Percent changes. You will then be able to tell which symbol is meeting certain criteria at a glance.

Here is how the quote window looks like before color coding.

Here is the color rule settings.

The quote window after using color code.

**Summary**

The relational operators, boolean operators and decision functions are usually used in combination all the time. They provide a way to distill the raw data and allowing you to obtain the information you really want based on your criteria.

**Exercise**

1. What is the formula for color coding a symbol green that gaps up and rising for more than 1%, yellow that gaps down and dropping for less than 0.5%, and black (blank) for all other cases?

2. What is the formula for color coding a symbol green when the last price is within 5% of today’s traded range from the high, and red when the last price is within 5% of today’s traded range from the low, and black for all other cases?

**Answers for Previous Exercise**

1. The function that finds the smallest number among multiple parameters is MinList. The formula is just

` MinList (DayClose, PrevClose)`

2. The most important function here is the maketime function which allows you to construct the time in a readable form. Here is the formula,

` (frac (NTnow) - maketime (9, 30, 0)) * 1440`

**Interact:**Add a Comment | Trackback Link | Permalink

**Share:**digg | del.icio.us | Technorati | StumbleUpon