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

## Thursday, November 29, 2012

### FLOOR Function in Salesforce Alternative Way

Labels:
Apex,
FLOOR,
FLOOR Excel,
FLOOR Formula,
FLOOR Function,
FLOOR Method,
FLOOR(number),
Formula Field,
Round,
Salesforce

Subscribe to:
Post Comments (Atom)

that would also work:

ReplyDeleteFLOOR(Amount / 10 ) * 10