You need to sign in to do that
Don't have an account?
Mildred Morales 19
HALF EVEN
HALF EVEN Rounding Formula
I need to use a something called Bankers Rounding in several formulas field in salesforce. This method is also called HALF EVEN. The only thing I can find is using APEX but I am wodering if anyone has been able to apply this using the standard formula screen. Here is the description
Rounds towards the “nearest neighbor” unless both neighbors are equidistant, in which case, this mode rounds towards the even neighbor. This rounding mode behaves the same as the HALF_UP rounding mode if the digit to the left of the discarded fraction (decimal point) is odd. It behaves the same as the HALF_DOWN rounding method if it is even. For example:
Input number 5.5: HALF_EVEN round mode result: 6
Input number 1.1: HALF_EVEN round mode result: 1
Input number -1.1: HALF_EVEN round mode result: -1
Input number -2.7: HALF_EVEN round mode result: -3
1 Decimal[] example = new Decimal[]{5.5, 1.1, -1.1, -2.7};
2 Long[] expected = new Long[]{6, 1, -1, -3};
3 for(integer x = 0; x < example.size(); x++){
4 System.assertEquals(expected[x],
5 example[x].round(System.RoundingMode.HALF_EVEN));
6}
Note that this rounding mode statistically minimizes cumulative error when applied repeatedly over a sequence of calculations.
I need to use a something called Bankers Rounding in several formulas field in salesforce. This method is also called HALF EVEN. The only thing I can find is using APEX but I am wodering if anyone has been able to apply this using the standard formula screen. Here is the description
Rounds towards the “nearest neighbor” unless both neighbors are equidistant, in which case, this mode rounds towards the even neighbor. This rounding mode behaves the same as the HALF_UP rounding mode if the digit to the left of the discarded fraction (decimal point) is odd. It behaves the same as the HALF_DOWN rounding method if it is even. For example:
Input number 5.5: HALF_EVEN round mode result: 6
Input number 1.1: HALF_EVEN round mode result: 1
Input number -1.1: HALF_EVEN round mode result: -1
Input number -2.7: HALF_EVEN round mode result: -3
1 Decimal[] example = new Decimal[]{5.5, 1.1, -1.1, -2.7};
2 Long[] expected = new Long[]{6, 1, -1, -3};
3 for(integer x = 0; x < example.size(); x++){
4 System.assertEquals(expected[x],
5 example[x].round(System.RoundingMode.HALF_EVEN));
6}
Note that this rounding mode statistically minimizes cumulative error when applied repeatedly over a sequence of calculations.
Apex: HALF_EVEN: Rounds towards the “nearest neighbor” unless both neighbors are equidistant, in which case, this mode rounds towards the even neighbor. This rounding mode behaves the same as the HALF_UP rounding mode if the digit to the left of the discarded fraction (decimal point) is odd. It behaves the same as the HALF_DOWN rounding method if it is even. For example:
Input number 5.5: HALF_EVEN round mode result: 6
Input number 1.1: HALF_EVEN round mode result: 1
Input number -1.1: HALF_EVEN round mode result: -1
Input number -2.7: HALF_EVEN round mode result: -3
Salesforce uses the round half-up rounding algorithm for ROUND in formulas.
But the HALF_DOWN rounding method has no standard function for the formulas in Salesforce.
CEILING and FLOOR are different rounding methods.
https://docs.oracle.com/javase/7/docs/api/java/math/RoundingMode.html
2.5 : HALF_EVEN = 2 = FLOOR
5.5 : HALF_EVEN = 6 = CEILING
There is a specific complicated formula to build.
As per my understanding i think i managed to achive your requirement
I have taken a Input_Number__c number field as input and output_result__c as farmula field ....
Here is the formula..
IF(
(Input_Number__c - FLOOR(Input_Number__c )) >= 0.5, CEILING(Input_Number__c ),
IF(
(Input_Number__c - FLOOR(Input_Number__c )) > 0 && (Input_Number__c - FLOOR( Input_Number__c ) ) < 0.5, FLOOR(Input_Number__c ) ,
IF(
(Input_Number__c - MCEILING(Input_Number__c )) >= -0.5,MCEILING(Input_Number__c) , MFLOOR(Input_Number__c)
)
)
)
Hope it will help you
Thank you for your reply. Using your formula everything is rounding to the dollar and not decimals :-(
Example
1,239.09 is now 1,239.00.
This field is set for currency 16,2
You can verify the exact values with RoundingMode.HALF_EVEN with the developer console (anonymous code)
1239.09 = HALF_EVEN = 1239 That is the correct value.
What are you expecting instead of 1239 ?
The notion of odd/even for the digit to the left of the discarded fraction (decimal point) seems missing in the Vaseem S's formula (not tested).
2.5 : HALF_EVEN = 2 => 2 is the digit to the left of the decimal point, is even => HALF_DOWN rounding method => 2
5.5 : HALF_EVEN = 6 => 5 is the digit to the left of the decimal point, is odd => HALF_UP rounding method => 6
-2.5 : HALF_EVEN = -2 => -2 is the digit to the left of the decimal point ,is even => HALF_DOWN rounding method => -2
-5.5 : HALF_EVEN = -6 => -5 is the digit to the left of the decimal point, is odd => HALF_UP rounding method => -6
-2.7 = HALF_EVEN = -3 : HALF_UP = -3 : HALF_DOWN = -3
-2.5 = HALF_EVEN = -2 : HALF_UP = -3 : HALF_DOWN = -2
The problem is the HALF_DOWN for the numbers with digit to the left of the decimal point that is even.
The numbers with digit to the left of the decimal point that is even could be tested by finding that the whole number is even by removing the decimal part.
HALF_UP is just ROUND() in SF formula.
I am expecting the number to be 1239.10 instead of 1239.09. Basically round to the next decimal because half even = true. Not to remove the cents from the total. This is a total from where I will need to cut a check and I can't short charge .9 cents instead I have to pay .10 cents