Today we will see salesforce apex equivalent code for Excel CORREL function. Excel CORREL function in Apex Salesforce can be used if we need to perform same calculation on salesforce data as excel CORREL function.
Syntax of CORREL function is as follows in Excel
- CORREL(array1, array2)
- WHERE array1 and array2 both are Required.
- We must ensure that array1 and array2 are same in size.
See the code below, code has static methods written you can put them in your util class/helper class.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
/** * @author : 799 The Coder * @date : 14 Aug 2017 * @description : This method returns a Decimal which is correlation coefficient of the Array1 and Array2 Similar to CORREL(array1, array2) in Excel, array1 is X and array2 is Y * @param : List<Decimal> arrayOne, List<Decimal> arrayTwo * @return : Decimal (Correlation Coefficient) */ public static Decimal correl(List<Decimal> arrayOne, List<Decimal> arrayTwo){ Decimal correlationCoefficient = 0.0; Decimal N = arrayOne.size(); Decimal avgX = average(arrayOne); Decimal avgY = average(arrayTwo); Decimal sumX_XBar_Mult_sumY_YBar = 0.0; Decimal sumX_XBar_SQR = 0.0; Decimal sumY_YBar_SQR = 0.0; for(integer i = 0; i < N; i++){ Decimal X_XBar = arrayOne.get(i) - avgX; Decimal Y_YBar = arrayTwo.get(i) - avgY; sumX_XBar_Mult_sumY_YBar += X_XBar*Y_YBar; sumX_XBar_SQR += X_XBar*X_XBar; sumY_YBar_SQR += Y_YBar*Y_YBar; } // CORRELATION COEFFICIENT = sumX_XBar_Mult_sumY_YBar / (SQRT(sumX_XBar_SQR*sumY_YBar_SQR)) correlationCoefficient = sumX_XBar_Mult_sumY_YBar / (MATH.sqrt(sumX_XBar_SQR*sumY_YBar_SQR)); return correlationCoefficient; } /** * @author : 799 The Coder * @date : 14 Aug 2017 * @description : This method returns a Decimal which is average of passed array * @param : List<Decimal> decimalSet * @return : Decimal (average) */ public static Decimal average(List<Decimal> decimalSet){ Decimal sum = 0.0; for(Decimal num : decimalSet){ sum += num; } return sum/decimalSet.size(); } |
Once we have above methods in our code, we can use it 🙂 , Now lets test the method from developer console, open developer console and write below code.
|
1 2 3 4 5 |
List<Decimal> arrayOne = new List<Decimal>{3,2,4,5,6}; List<Decimal> arrayTwo = new List<Decimal>{9,7,12,15,17}; Decimal correlationCoeffient = ExcelFormulaUtils.correl(arrayOne, arrayTwo); // Use your class name instead of ExcelFormulaUtils in above statement. System.debug('####> correlationCoeffient = ' + correlationCoeffient); |
When you execute the above code, you will get the output as follows.
correlationCoeffient = 0.997054485501581486225379535213640
Hope this will help you 🙂
See more Excel functions in apex salesforce code
Happy Coding !
799 The Coder