Apex Code Development You need to sign in to do that
Don't have an account?
DaveAdams2 Round up Numbers
Using the formula editor, how do i round up (or down) a number to x decimal places. eg:- I would like 1.239 rounded up to 2 decimal places to get the result 1.24. thanks!
ROUNDDOWN: FLOOR(100*Temp__c)/100
ROUNDUP: CEILING(100*Temp__c)/100
Werner
All Answers
Use the ROUND function
(page 80)
https://na3.salesforce.com/help/doc/en/salesforce_useful_formula_fields.pdf
Just to clarify, I would like to force a round up. So if i had 1.231, and wanted to roud up to 2 decimal places, it would show 1.24. I don't think the normal round function would behave like this.
thanks!
Dave
I'm not sure how you would "force" that, in your initial post you said
"1.239 to 1.24" that's basically a normal Round-Up
are you now saying that you need to override the normal Round-Up and force a Round-Down?
ROUNDDOWN: FLOOR(100*Temp__c)/100
ROUNDUP: CEILING(100*Temp__c)/100
Werner
To do this, you need to apply a little addtional mathmatical logic to the regular ROUND function.
For the first example, let's ignore the CEILING and FLOOR functions, and just round up using the normal ROUND function. To accomplish a ROUND UP, we just add half of the amount that we're rounding to. Since we're rounding to the ones digit, we add 0.5 before we apply the ROUND function.
So, if we take 1.4, and use ROUND, it will just ROUND down to 1.
ROUND(1.4,0) = 1
Instead, we add 0.5 first:
ROUND(1.4+0.5, 0) = ROUND(1.9, 0) = 2
This also works if the number would have rounded up anyway:
ROUND(5.9, 0) = 6
ROUND(5.9+0.5,0) = ROUND(6.4, 0) = 6
The end result is that will always round up by adding the 0.5 (for integers).
Now, to get to decimal places, we have to change two things:
1) We need change the amount we're adding, to be half of the digit we're rounding to. So, if we're rounding to the tenths, we add 0.05. To the hundreths, we add 0.005.
2) We change the number of digits we're rounding to in the ROUND function.
ROUND(value, X) <-- the X has to change
Examples:
RoUND(6.23,1) = 6.2
ROUND(6.23+0.05, 1) = ROUND(6.28, 1) = 6.3
ROUND(9.452, 2) = 9.45
ROUND(9.452+0.005, 2) = ROUND(9.457, 2) = 9.46
If you want to round down, you just need to subtract the half digit instead of adding it.
Hope this helps!
- Justin
Christina
Justin, Great formula! However I would caution that it may not produce the desired result if the number being rounded is exactly a multiple of one of the desired increments. To explain:
ROUND(100,0) = 100
ROUND(100+0.5,0) = 101 which is probably not the answer people would want.
One way around this is to add just less than half a digit. Typically you would want to do this to one decimal place greater than the possible decimals in your number. For example:
ROUND(100+0.499,0) = ROUND(100.499,0) = 100 but anything greater than 100.001 would round up to 101: ROUND(100.001+0.499,0) = ROUND(100.5,0) = 101
Excel FLOOR - Rounds value down to nearest divisor.
Excel: FLOOR(Value__c, Divisor__c)
Example: FLOOR(137, 50) = 100
Salesforce: Value__c - MOD(Value__c,Divisor__c) - IF(Value__c < 0, Divisor__c, 0)
Excel CEILING - Rounds value up to nearest divisor.
Excel: CEILING(Value__c, Divisor__c)
Example: CEILING(137, 50) = 150
Salesforce: Value__c - MOD(Value__c, Divisor__c) + IF(Value__c < 0, 0, Divisor__c)
For the original posed question, you can just tweak the formula to be MCEILING(100*Temp__c)/100
I'm not sure when this function was introduced, but it was new to me and I'm glad it's now in the functions!