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!

wgraTry 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

## All Answers

Steve :-/

Use the ROUND function

(page 80)

https://na3.salesforce.com/help/doc/en/salesforce_useful_formula_fields.pdf

DaveAdams2

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

Steve :-/

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?

DaveAdams2Example 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 Steve :-/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. DaveAdams2Thanks anyway! wgraTry 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

DaveAdams2This works great. Thanks for the help! gslagle1.3916088682906562E12The 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 13Justin, 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 JamisonJustin, 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.3916088682906562E12Neil - good catch! Andre Thouin 5How can I round to the nearest 100$ increment? can I use -2 as a round(number,-2) parameter? Dennis H PalmerFor 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) = 100Salesforce: 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) = 150Salesforce: Value__c - MOD(Value__c, Divisor__c) + IF(Value__c < 0, 0, Divisor__c)Cathy PostmusThanks, Dennis H Palmer! That formula helped me round up to the nearest 10. Much appreciated! DeeptiShuklaMath.Round(); Simply works well to round off a number. Christopher_Alun_LewisFor 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:

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!