+ Start a Discussion
DaveAdams2DaveAdams2 

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!
Best Answer chosen by Admin (Salesforce Developers) 
wgrawgra
Try these formulas whereas "Temp__c" is the field to round up or round down:

 

 

ROUNDDOWN: FLOOR(100*Temp__c)/100

ROUNDUP: CEILING(100*Temp__c)/100

 

Werner

 

 

Message Edited by wgra on 02-02-2010 08:44 AM

All Answers

DaveAdams2DaveAdams2

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

SteveMo__cSteveMo__c

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?

DaveAdams2DaveAdams2
Example I used first was a bad one. From what I understand, the normal ROUND function will round up or down automatically, depending which number is closer. So using the ROUND function to 2 decimal places with 1.347 would auto round up to 1.35 . Using the same ROUND function 1.343 would automatically round down to 1.34 . I want a function that will round up the number every time. So if the number is 1.347 or 1.343, i always get the answer 1.35 when rounding to 2 decimal places. Conversly, i would also want a function that will round down the number every time. So if the number is 1.347 or 1.343, i always get the answer 1.34 when rounding to 2 decimal places. If you are familiar with ms excel, the two functions i am after are called ROUNDUP and ROUNDDOWN
SteveMo__cSteveMo__c
I think you're probably looking at writing your own formula that evaluates the complete string, and manually rounds-up, and I can't help you there.
DaveAdams2DaveAdams2
Thanks anyway!
wgrawgra
Try these formulas whereas "Temp__c" is the field to round up or round down:

 

 

ROUNDDOWN: FLOOR(100*Temp__c)/100

ROUNDUP: CEILING(100*Temp__c)/100

 

Werner

 

 

Message Edited by wgra on 02-02-2010 08:44 AM
This was selected as the best answer
DaveAdams2DaveAdams2
This works great. Thanks for the help!
gslagle1.3916088682906562E12gslagle1.3916088682906562E12
The CEILING and FLOOR functions worked great for me for rounding to the nearest integer.  However, if you're looking to round to decimal places, this doesn't quite work. 

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 Moore 13Christina Moore 13
Justin, I was able to use the logic behind your formula to solve my issue of rounding to the nearest quarter by adding 0.125 to the number (within a larger formula).  Thank you! Thank you! Thank you!

​Christina 
Neil JamisonNeil Jamison

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

gslagle1.3916088682906562E12gslagle1.3916088682906562E12
Neil - good catch!
Andre Thouin 5Andre Thouin 5
How can I round to the nearest 100$ increment? can I use -2 as a round(number,-2) parameter?
Dennis H PalmerDennis H Palmer
For anyone who is wanting to recreate Excel FLOOR & CEILING in a formula.  This solves for negative numbers as well.

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)
Cathy PostmusCathy Postmus
Thanks, Dennis H Palmer! That formula helped me round up to the nearest 10. Much appreciated!
DeeptiShuklaDeeptiShukla
Math.Round(); Simply works well to round off a number.
Christopher_Alun_LewisChristopher_Alun_Lewis
For those still looking for an answer on this, as I was today, use the MCEILING formula function. This function always rounds up a number to the nearest integer. I made a number field (Number__c) on an object then created a fromula that was just MCEILING(Number__c). Here are the results:

User-added image
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!