Hello awesome Developers!!! This post will save your time if you are going to implement Excel INTERCEPT function in apex salesforce. In my previous post of excel function (Excel LINEST function in Apex Salesforce), we coded for LINEST function of excel.
Before we jump to code, lets have a quick view of INTERCEPT function.
Syntax of Intercept function is as follows in Excel
INTERCEPT(known_y’s, known_x’s)
WHERE Known_y’s and Known_x’s both are Required.
We must ensure that Known_y’s and Known_x’s size is same.
|
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 |
/** * @author : 799 The Coder * @date : 07 Aug 2017 * @description : This method returns a Decimal which is INTERCEPT of passed known Ys and known Xs numner set. Equivalent to INTERCEPT(known_y's, known_x's) * @param : List<Decimal> knownYs, List<Decimal> knownXs * @return : Decimal (intercept) */ public static Decimal intercept(List<Decimal> knownYs, List<Decimal> knownXs){ Decimal intercept = 0.0; Decimal sumY = 0.0; Decimal sumX = 0.0; Decimal sumXY = 0.0; Decimal sumXSQ = 0.0; Decimal N = knownYs.size(); for(integer i = 0; i < N; i++){ sumY += knownYs.get(i); sumX += knownXs.get(i); sumXY += knownXs.get(i)*knownYs.get(i); sumXSQ += knownXs.get(i)*knownXs.get(i); } // INTERCEPT = ((sumXSQ*sumY) - (sumX*sumXY)) / ((N*sumXSQ) - (sumX*sumX)) intercept = ((sumXSQ*sumY) - (sumX*sumXY)) / ((N*sumXSQ) - (sumX*sumX)); return intercept; } |
The above code snippet is a static method, you can put it any helper/utility class in salesforce. See below a demo call to this method from developer console.
|
1 2 3 4 |
List<Decimal> knownYs = new List<Decimal>{2,3,9,1,8}; List<Decimal> knownXs = new List<Decimal>{6,5,11,7,5}; System.debug('INTERCEPT = '+ ExcelFormulaUtils.intercept(knownYs, knownXs)); // USE YOUR Class name for 'ExcelFormulaUtils' |
The output of the above code snippet from developer console will be below
INTERCEPT = 0.0483870967741935483870967741935484
Use it and save your time, in your saved time do some other creative coding 🙂 !!!