Hi everyone:
Business Scenario:
The business would like a formula field that will calculate a number to the nearest lowest 10.
Which means:
If the number is 129 the new number would be 120
If the number is 64 the new number would be 60.
The issue:
In Salesforce, FLOOR Functionality does not work the same way in Excel.
In Excel you would do something like this to get the top results:
=FLOOR(129,10) which equals 120
In Salesforce, within a formula field it would be:
FLOOR(129) which equals 129, does not work. Do not know why, when Salesforce states the following:
"Returns a number rounded down to the nearest integer."
Solution:
Navigate to the Object that you are placing this formula field in: For me its:
Setup | Create | Object | Test | New Field
Click on Formula Attribute type and Click Next.
For data type click on Number, no decimal if you want you can have it.
The field I will utilize is an existing field within my formula that is a Number(18,0) field already named Net_Enrollment__c.
This is my final formula for the new field I created named Level__c:
IF(Net_Enrollment__c <1 1="1" et_enrollment__c="et_enrollment__c" span="span">1>
Breakdown of the formula:
I utilize If then statement to conditionalize it to work properly (you do not have to)
I start with:
IF(Net_Enrollment__c<1 0="0" 1="1" be="be" do="do" end="end" greater="greater" if="if" is="is" it="it" math="math" means="means" net_enrollment="net_enrollment" result="result" should="should" span="span" the="the" then="then" which="which" zero="zero">1>
FLOOR(Net_Enrollment__c) <== which returns me my number
VALUE(RIGHT(TEXT(Net_Enrollment__c), 1) <== Reasons of utilizing TEXT functionality and RIGHT Function
RIGHT Function only works on TEXT and not number data types.
So I had to convert the Net_Enrollment__c field into a TEXT and then utilize the RIGHT function to get me the Last Digit in my Net Enrollment number and VALUE function returns it to a number
So if I have 129, by using the formula
VALUE(RIGHT(TEXT(129), 1) would give me "9"
So then I take the whole number 129 - 9 = 120
Which is
FLOOR(129)-VALUE(RIGHT(TEXT(129), 1))
Hope this helps and remember always think outside the box.
Thanks
Zishan
Salesforce.com / Force.com Tips & Tricks - Learn Apex, Page Layouts, SOQL, Certification, plus more!!
Authored by Zishan Razzaq
Showing posts with label FLOOR Formula. Show all posts
Showing posts with label FLOOR Formula. Show all posts
Thursday, November 29, 2012
Subscribe to:
Comments (Atom)