Apex Code Development You need to sign in to do that
Don't have an account?
Aaliya Yusufzai Formula Field to Round up or down by mins
Hi,
I'm trying to create a formula field to round up to either 1.00 or 1.50 or 2.00 or 3.00. There will not ever be anything over
The formula field is as follows: ROUND(X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c,1)
These are driven by products selected and rounds into minutes. Example of roll-up: if .80, then it should display 1.00, and if 1.10, than it should show up as 1.50 and if it shows 1.60, it should show as 2.00 and if it shows 2.10 or anything over, it will all round up/down to 3.00 So it's a roundup. How can I configure this formula field.
I'm trying to create a formula field to round up to either 1.00 or 1.50 or 2.00 or 3.00. There will not ever be anything over
The formula field is as follows: ROUND(X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c,1)
These are driven by products selected and rounds into minutes. Example of roll-up: if .80, then it should display 1.00, and if 1.10, than it should show up as 1.50 and if it shows 1.60, it should show as 2.00 and if it shows 2.10 or anything over, it will all round up/down to 3.00 So it's a roundup. How can I configure this formula field.
Let's call your X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c formula BASE for the sake of simplicity. You'll just replace the BASE with that formula at the end.
This formula will be a little lengthy but I'll walk you through it.
If it wasn't for the 1.50 value this would be pretty much just a rounding up formula but since one of your values isn't a whole number we have to account of that by multipling BASE by 2 (so 1.5 turns into a whole number) and then rounding then diving by 2.
In SalesForce to round up you use a formula called CEILING, fyi.
Here is the first bit of formula we'll use: CEILING(BASE*2,0)/2
As you can see we are multpily BASE by 2, rounding it up with CASE, and then dividing by 2.
Here is a table showing what this formula will return:
So you can see we only have 4 possible returned values: 0.5, 1, 1.5, and 2.
The function CASE allows you to return defined values for defined inputs and an "else result".
When we input 0.5 we want to return a value 1, for the inputs of 1, 1.5, and 2 we just want to return the same value, and the else result will be 3.
Here is our formula:
CASE(CEILING(BASE*2,0)/2,0.5,1,1,1,1.5,1.5,2,2,3)
To break it down:
CASE(
CEILING(BASE*2,0)/2, <-what is being input
0.5,1 <-if the input equals 0.5 return 1
1,1 <-if the input equals 1 return 1
1.5,1.5 <-if the input equals 1.5 return 1.5
2,2 <-if the input equals 2 return 2
3) <-else return 3
That all said, I think this is your final formula:
CASE(CEILING((X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c)*2,0)/2,0.5,1,1,1,1.5,1.5,2,2,3)
Let me know if you need any clarification.
If you found this post helpful please "Like" it.
Chris
So, just to be clear regarding my request.
Example: Anytime certain products are selected, i have a rule to add 10mins in X10Min_ofHours__c. So I could have 30mins in that field as I may have 3 products.
Then their are products that automatically add 15mins into the X15Min_ofHours__c field. I may have 2 products that will automatically add 40mins to that field.
What I need is to create a formula field to add these to round up in minutes. We may need a ceiling or floor as the mins may go up to say 3.20, but i need that to show up as 3.00 and if it is .20, then it should round up to 1.00.
The only Round up, I require are for 1.00 or 1.50 or 2.00 or 3.00.
Hope that makes sense.
Thanks!
Aaliya
Give this formula a try :
IF((X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c)<1,1,IF(AND((X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c)>1,(X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c)<=1.5),1.5,IF(AND((X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c)>1.5,(X10Min_ofHours__c + X15Min_ofHours__c + X20Min_ofHours__c + X45Min_ofHours__c)<=2),2,3)))
Hope this helps!
Please mark this question as Solved if you think your question was answered, so that others could view it as a proper solution.
Thanks,
Apoorv