Excel function in Apex Archives

Excel FISHER Function in Apex Salesforce

In this post we will see how we can write Excel FISHER function in Apex Salesforce. Before going to the apex code lets first see some details about FISHER function. This function returns the fisher transformation at x.

Syntax of FISHER function is as follows in Excel

FISHER(x)
WHERE x is required
Value of x should be greater than -1 and less than 1 i.e -1 < x < 1.

Now see the code for Excel FISHER function in Apex Salesforce.

/**
 * @author : 799 The Coder
 * @date : 21 Aug 2017
 * @description : This method returns Fisher transformation at x, equivalent to FISHER(x) excel function
 * @param  : Decimal x (-1 < x < 1)
 * @return : Decimal (fisher) 
 */
public static Decimal fisher(Decimal x) {
  Decimal fisher = 0.0;
  fisher = (Math.log((1 + x) / (1 - x))) / 2;
  return fisher;
}

/**

* @author : 799 The Coder

* @date : 21 Aug 2017

* @description : This method returns Fisher transformation at x, equivalent to FISHER(x) excel function

* @param : Decimal x (-1 < x < 1)

* @return : Decimal (fisher)

public static Decimal fisher(Decimal x) {

Decimal fisher = 0.0;

fisher = (Math.log((1 + x) / (1 - x))) / 2;

return fisher;

}

Above code can be added in any apex class, after adding this code we can use it as per our requirement. Lets test it from developer console. Go to developer console and in Execute anonymous window paste the below code.

Decimal x = 0.75;
Decimal fisher = ExcelFormulaUtils.fisher(x);
// Use your class name instead of ExcelFormulaUtils in above statement.
System.debug('####>  fihser = ' + fisher);

Decimal x = 0.75;

Decimal fisher = ExcelFormulaUtils.fisher(x);

// Use your class name instead of ExcelFormulaUtils in above statement.

System.debug('####> fihser = ' + fisher);

After executing above code snippet you will get below output.

Excel Fisher Function In Apex Salesforce — Output in Developer console after selecting Debug

Hope this code will save your time, if you need to implement FISHER function in salesforce.

Happy Coding 🙂
799 The Coder

See some more post for Excel function in Apex

Excel CORREL function in Apex Salesforce

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.

/**
    * @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();
    }

/**

* @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.

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);

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

Excel INTERCEPT function in Apex Salesforce

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.

/**
* @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;
}

/**

* @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.

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'

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 🙂 !!!

Excel LINEST function in Apex Salesforce

As a developer we sometime need to implement excel function in code for meeting client’s requirement. Here I will share code for Excel LINEST function in apex. You can check LINEST function/method of excel here.

See below code method, you can use it in your apex class.

/**
 * @author : 799 The Coder
 * @date : 1 Feb 2016
 * @description : This method returns a Decimal which is slope for the passed  List of y coordinates (Equivalent to LINEST(y's,,TRUE,TRUE))
 * @param  : List<Decimal> ySet
 * @return : Decimal (slope) 
 */
public static Decimal getSlopeFromYset(List < Decimal > ySet) {
    Decimal slope = 0.0;
    Decimal sumX = 0.0, sumY = 0.0, sumXY = 0.0, sumX2 = 0.0;
    List < Decimal > xSet = new List < Decimal > ();
    // Setting up set of X with default set {1,2,3,...n} 
    decimal x = 1;
    for (Decimal tempY: ySet) {
        xSet.add(x++);
    }
    // No of elements in Y
    integer N = ySet.size();
    for (integer i = 0; i < ySet.size(); i++) {
        Decimal xy = xSet.get(i) * ySet.get(i);
        Decimal x2 = xSet.get(i) * xSet.get(i);
        sumX = sumX + xSet.get(i);
        sumY = sumY + ySet.get(i);
        sumXY = sumXY + xy;
        sumX2 = sumX2 + x2;
    }
    slope = ((N * sumXY) - (sumX * sumY)) / ((N * sumX2) - (sumX * sumX));
    return slope;
}

/**

* @author : 799 The Coder

* @date : 1 Feb 2016

* @description : This method returns a Decimal which is slope for the passed List of y coordinates (Equivalent to LINEST(y's,,TRUE,TRUE))

* @param : List<Decimal> ySet

* @return : Decimal (slope)

public static Decimal getSlopeFromYset(List < Decimal > ySet) {

Decimal slope = 0.0;

Decimal sumX = 0.0, sumY = 0.0, sumXY = 0.0, sumX2 = 0.0;

List < Decimal > xSet = new List < Decimal > ();

// Setting up set of X with default set {1,2,3,...n}

decimal x = 1;

for (Decimal tempY: ySet) {

xSet.add(x++);

}

// No of elements in Y

integer N = ySet.size();

for (integer i = 0; i < ySet.size(); i++) {

Decimal xy = xSet.get(i) * ySet.get(i);

Decimal x2 = xSet.get(i) * xSet.get(i);

sumX = sumX + xSet.get(i);

sumY = sumY + ySet.get(i);

sumXY = sumXY + xy;

sumX2 = sumX2 + x2;

}

slope = ((N * sumXY) - (sumX * sumY)) / ((N * sumX2) - (sumX * sumX));

return slope;

}

It will save your time if you need to write it from scratch in apex. I am open for your valuable feedback.

Happy Coding 🙂

Tag Archives: Excel function in Apex