+ Start a Discussion

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





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

All Answers


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.






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?

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
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.
Thanks anyway!
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





Message Edited by wgra on 02-02-2010 08:44 AM
This was selected as the best answer
This works great. Thanks for the help!
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
     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!

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

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!
Math.Round(); Simply works well to round off a number.
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!