Authored by Zishan Razzaq

Thursday, November 29, 2012

FLOOR Function in Salesforce Alternative Way

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

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

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

 

1 comment: