# Calculations with formulas

How to:

## Annualize a monthly P/P series with a formula?

Use the following expression:

pow((1+(series/100)), YearLength())-1

The expression “1+ (series / 100)” will be raised to the power of “yearlength()”. After which 1 will be subtracted from the result.

## Change the color of a series when it’s below / above 0 (or any other value)?

1. Create two series, one containing only the values above 0 and another containing the values below 0  and then graph each series in a different color. Do the following:
a) In the series list, type the expressions:

if(series >0, series, Null())

if(series <0, series, Null())

b) In the chart, click on a graph and open the Presentation properties tab. Select “Custom” from the graph style drop-down menu in the appearance group. Select the color of your choice.

2. Flag the values below 0 as forecast values, and change the color in which forecast values are graphed, by doing the following:
a) In the series list, type the expression:

if(series > 0, series, flagforecast(series))

b) In the chart, click on the graph of the series and open the presentation properties tab. Select “custom” from the graph style drop-down menu in the appearance group. Click “forecast.” Select the color of your choice.

## Create an if condition/statement?

Formula language can be used in both the series list and the formula analysis. The if-statement is a formula requiring three parameters:

if(condition, value1, value2)

which can be expanded on as:

if(condition, if_TRUE_return_this , if_FALSE_return_this)

For example:

if(sek > 8, sek, 0)

Which returns a series with values of 0 on days when the series is below 8 and values equaling the series when the currency pair is above 8.

## Create a continuous growth rate?

To create constantly growing line use formula for compound interest:

AggregateProduct(1+VALUE/100)*100

for example 5% continuous growth rate would be:

AggregateProduct(1+0.05/100)*100

## Combine an if condition/statement with the logical operators and/or?

To include “and” in the function use the “&” sign as such:

if(condition1 & condition2, value1, value2)

which can be expanded on as:

if(condition1 and condition2, if_TRUE_return_this , if_FALSE_return_this)

For example:

if(sek>7 & sek<8, sek, 0)

which returns the values of the series on days when the series is between 7 and 8. For all other observations, the value of the series will be 0.

To include “or” in the function use the “|” sign as such:

if(condition1|condition2, value1, value2)

which can be expanded on as:

if(condition1 OR condition2, , if_TRUE_return_this , if_FALSE_return_this)

For example:

if(sek>7 | sek<9, sek, 0)

which returns the values of the series on days when the series is above 7 or below 9. For all other observations, the value of the series will be 0.

## Make an index out of P/P series?

To create an index from a return series use formula for compound interest:

AggregateProduct(1+(series)/100)*100

The '*100' creates here starting base value.

Note that for different series you might need to transform this calculation.

## Disaggregate a series?

Series which are aggregated annually can be “deaggregated” by using an If() statement in the formula language.

Example:

If(Counter()=StartOfYear(), cngpfi0091, Momentum(cngpfi0091, 1))

The logic of this formula is: keep the first value of each year as given, but the subsequent values in that year are calculated by subtracting the previous value from the current value.

## Replace null values for 0 in a time series?

You can use the function:

Null0(series)