RangeCorrel - script and chart function

RangeCorrel() returns the correlation coefficient for two sets of data. The correlation coefficient is a measure of the relationship between the data sets.

Syntax:  

RangeCorrel(x_value , y_value[, Expression])

Return data type: numeric

Data series should be entered as (x,y) pairs. For example, to evaluate two series of data, array 1 and array 2, where the array 1 = 2,6,9 and array 2 = 3,8,4 you would write RangeCorrel (2,3,6,8,9,4) which returns 0.269.

Arguments:  

Limitations:  

The function needs at least two pairs of coordinates to be calculated.

Text values, NULL values and missing values return NULL.

Examples and results:  

Example:

Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.

RangeList:

Load * Inline [

ID1|x1|y1|x2|y2|x3|y3|x4|y4|x5|y5|x6|y6

01|46|60|70|13|78|20|45|65|78|12|78|22

02|65|56|22|79|12|56|45|24|32|78|55|15

03|77|68|34|91|24|68|57|36|44|90|67|27

04|57|36|44|90|67|27|57|68|47|90|80|94

] (delimiter is '|');

 

XY:

LOAD recno() as RangeID, * Inline [

X|Y

2|3

6|8

9|4

8|5

](delimiter is '|');

 

In a table with ID1 as a dimension and the measure: RangeCorrel(x1,y1,x2,y2,x3,y3,x4,y4,x5,y5,x6,y6)), the RangeCorrel() function finds the value of Correl over the range of six x,y pairs, for each of the ID1 values.

 

Example:  

XY:

LOAD recno() as RangeID, * Inline [

X|Y

2|3

6|8

9|4

8|5

](delimiter is '|');

 

In a table with RangeID as a dimension and the measure: RangeCorrel(Below(X,0,4,BelowY,0,4)), the RangeCorrel() function uses the results of the Below() functions, which because of the third argument (count) set to 4, produce a range of four x-y values from the loaded table XY.

The value for RangeID 01 is the same as manually entering RangeCorrel(2,3,6,8,9,4,8,5). For the other values of RangeID, the series produced by the Below() function are: (6,8,9,4,8,5), (9,4,8,5), and (8,5), the last of which produces a null result.