I was recently asked to convert an Excel spreadsheet into a Crystal Report. I found that the spreadsheet used some Excel statistical functions that don’t exist in Crystal, so I had to replicate those in Crystal syntax. The functions calculated the slope, intercept and correlation of a series x-y coordinates. Once I had them working I thought I would post them here. That way I can find them the next time I need them.

To use these you need to be able to create two arrays in Crystal. One for all of the [x] coordinates and one for all of the corresponding [y] coordinates. Then you reference the arrays in these formulas. Not that the intercept refers to the slope formula.

Slope:
WhilePrintingRecords;
NumberVar array x; //array of all the x coordinates
NumberVar array y; //array of all the y coordinates

Local NumberVar array xx := x; //summation of x squared
Local NumberVar array xy := x; //summation of x*y

Local NumberVar i;
for i := 1 to count (xx)
do (xx [i] := x[i]^2 ; xy[i] := x[i]*y[i]) ;

( count(x) * sum(xy) – sum(x)* sum(y) ) / (count(x) * sum(xx) – sum(x)^2)

Intercept:
WhilePrintingRecords;
NumberVar array x;
NumberVar array y;

(sum(y) – ({@Slope} * Sum(x))) / Count (x)

Correlation:
WhilePrintingRecords;
NumberVar array x;
NumberVar array y;

Local NumberVar array z := x ;
Local NumberVar i;

for i := 1 to count (x)
do (z[i] := (x[i] – Average (x)) * (y[i] – Average (y)));

( Sum(z) / (StdDev (x) * StdDev(y)) ) / (Count (x)-1)