MCEILING() Function in Salesforce

In this Salesforce tutorial, we will learn the Salesforce MCEILING() function. In addition, we will learn the syntax and some practical scenarios where we can use this function in Salesforce.

Recently, I worked for a New York-based company that uses the CRM tool Salesforce to run its business efficiently. While working here, I got a task where I need to calculate the number of leads required to fulfill the target.

As a solution, I discovered that Salesforce gives us the function named “MCEILING” function that helps in calculating the round-off value nearest to the integer. So, let’s go into more detail and learn how to use the MCEILING() function in Salesforce Lightning and Salesforce Classic.

MCEILING() Function in Salesforce

The MCEILING() function in Salesforce is the mathematical function available in Salesforce that is used to carry out mathematical calculations.

Basically, this MCEILING() function is used to round the numerical value up to the nearest integer, and if the numerical value is negative in nature it rounds off toward zero.

In addition, the MCEILING() function only supports numerical data types such as Integers, Decimals, and Currency also. But if you try to use the text, date, or other non-numeric data type, it will produce an error.

The syntax of the Salesforce MCEILING() function is as given below:

MCEILING(number/field/formula)

The parameter number/field/formula is the value for which we are trying to round off the value. The value of the parameter can be a field, a constant number, or a complex formula.

Now, let’s see an example to completely understand the MCEILING() function:

As I have discussed above that I need to calculate the number of leads required to fulfill the target. So for this, I will use the MCEILING() function of Salesforce. Here, is the formula:

MCEILING(No_Of_Leads__c)

With this, we have learned MCEILING() function, its syntax, and also an example. Now, we will see the difference between the MCEILING and CEILING functions of Salesforce.

Read MOD() Function in Salesforce | Calculate remainder in Salesforce

MCEILING() Function vs CEILING() Function in Salesforce

The major difference between the Salesforce MCEILING() function and the CEILING() function is as follows:

MCEILING() function: The MCEILING function rounds a number up to the nearest integer value. If the number is negative it rounds towards the zero.

CEILING() function: The CEILING function rounds a number up to the nearest integer value. If the number is negative it rounds the number away from zero.

Now, we will move ahead and learn to use this MCEILING() function in Salesforce Lightning.

How to use MCEILING() Function in Salesforce Lightning

Here, are the steps to use MCEILING() Function in Salesforce Lightning:

Step 1: Log in to Salesforce Lightning mode, then click the “Gear Icon” in the top right corner of the page. Click “Setup” from the dropdown menu to continue.

Step 2: After clicking on the Setup option, the setup page is open. Click on the “Object Manager” that is present in the Navigation Bar near the Home tab.

Step 3: After clicking on it, the Object Manager page is open. Here we can see lots of objects are present. Search for the object in the search bar where we wish to utilize the MCEILING() function. In this case, I search for the “Order” object as I want to count the total number of products required to fulfill an order.

Step 4: Now, click on the “Order” object and it will redirect us to the order object page.

Step 5: Under this order object page, there is a details section on the left side of the page in which the “Field & Relationship” option is present. Click on the “Field & Relationship” option. 

Step 6: After clicking on the field and relationship option, the field and relationships page is open. For using the MCEILING function select the field type by clicking on the “New” button that is located at the top of the field and relationship page.

Step 7: After clicking on the new button, the data type page is open. There are lots of data types present on the page. Here we are selecting the Formula data type field that is present on the page. The formula field allows users to use the MCEILING() function.

Step 8: To proceed to the next step, click on the “Next” button.

Step 9: After clicking on the next button, the Choose Output Type page is open. Now enter the details, first fill the “Field Label” and then fill the “Field Name” which automatically fills by simply clicking on the field. In this case, I entered the field label named “Product Needed”.

Step 10: Choose the “Output Data Type” for the field for which we want to calculate the product quantity.

Here, I select the data type as “Number” to get the formula return type as a number, so I check the radio button in front of it and I also set the “Decimal Place” to 3.

Step 11: Move to the next step by clicking on the “Next” button.

Step 12: Move to the advanced formula editor, and enter the formula that uses the MCEILING() function. In this case, I want to calculate the quantity of the product needed using the respective function. Here, is the formula:

MCEILING( Order_Quantity__c / Items__c) * Items__c

• Here, we first divide the order quantity field “Order_Quantity__c” by the item field “Items__c” and then pass it to the MCEILING() function to get the result value to the nearest integer, towards zero if negative.
• Next, multiply the resultant value by the item field “Items__c”.

Step 13: Click on the “Check Syntax” button to validate the formula and ensure that there are no errors.

Step 14: Optionally, we can “Handle the empty field” as well as we can define the “Description” and “Help text” if we want.

Step 15: Click on the “Next” button to move to the formula field-level security step.

Step 16: Now established the “Field-Level Security”. Now select the profiles to which we want to grant edit access to this field via field-level security. The field will be hidden from all profiles if we do not add to it field-level security.

I have selected “Visible” in this case because I want it to be visible to every profile. Then, click on the “Next” button that is present at the top.

Step 17: After establishing the field-level security, now add the custom field to the “Page Layout”. Select the page layout that should include this field. This field will be added as the last field of this page layout. The field will not appear on any pages if we do not select a layout.

Step 18: Click on the “Save” button to save the formula field.

We can use the formula field once we’ve created it. Let’s look at an example:

• Open the Orders Tab and create a new order with the fields Item and Order Quantity.
• After that, click on the Details section, and here the Product Needed field appears which shows you the result of the MCEILING() function.

As a result, we now know how to use MCEILING() Function Salesforce Lightning. Now, we’ll look at how to use MCEILING() Function Salesforce Classic.

Read MFLOOR() Function in Salesforce

How to use MCEILING() Function Salesforce Classic

Here, are the steps to use MCEILING() Function in Salesforce Classic:

Step-1:  First log in to the Salesforce Account, when we log in to the account the Salesforce lightning edition is open. Moving to Salesforce Classic, click on “Profile Picture”. The Profile picture drop-down list is open. Under this profile picture list, there is an option to “Switch To Salesforce Classic”. Click on it.

Step 2: After that, click on the Setup option.

Step 3: After clicking on the setup option, the setup page is open. Now, scroll down to the Build section. Then under Customize, select the object for whom field we want to use the MCEILING() function. In this case, I select the “Price Book” object as I want to track the minimum price of each book to achieve the target.

Step 4: Click on the “Price Books” drop-down menu. The drop-down list is open, under this drop-down list there is a field option. Click on the “Fields” option.

Step 5: After clicking on the fields option, the Price Book custom field page is open. Scroll to the bottom of the page there is a “Price Book custom field and a relationship” section. For creating the new field click on the “New” button.

Step 6: After clicking on the new button, the data type page is open. There are lots of data types present on the page. Here we are selecting the “Formula” data type field that is present on the page.

Step 7: To proceed to the next step, click on the “Next” button.

Step 8: After clicking on the next button, the “Choose Output Type” page is open. Enter the information now by filling out the “Field Label” and the “Field Name”, which is automatically filled in when you click on the field. I typed “Minimum Price” into the field label in this instance.

Step 9: Select the “Output Data Type” for the field where the minimum price will be calculated.

Here, I select the data type as “Currency” to get the formula return type as a currency, so I check the radio button in front of it and I also set the “Decimal Place” to 3.

Step 10: Move to the next step by clicking on the “Next” button.

Step 11: Now, go to the advanced formula editor and type the MCEILING() function formula there. In this instance, I want to use the MCEILING function to determine the minimum price of the book needed to achieve the revenue target. The formula is as follows:

MCEILING(Revenue__c / No_Of_Books__c)

Here, we divide the revenue field “Revenue__c” by the no of books field “No_Of_Books__c” and then pass it to the MCEILING() function to get the result value to the nearest integer, towards zero if negative.

Step 12: To validate the formula and make sure there are no mistakes, click the “Check Syntax” option.

Step 13: Optionally, we may define the “Description” and “Help text” as well as “Handle the empty field” if we like.

Step 14: Click the “Next” button to go to the security page for the formula field.

Step 15: Now established the “Field-Level Security”. Now choose the profiles to which we wish to grant field-level security edit access to this field. If field-level security is not added, the field will be hidden from all profiles.

Because I want it to be visible to all profiles, I have chosen “Visible” in this instance. Next, select the “Next” button located at the top to move ahead.

Step 16: Add the custom field to the “Page Layout” after setting up the field-level security. Select the page layout that has this field in it. If we do not select a layout, the field will not appear on any pages.

Step 17: Click on the “Save” button to save the formula field.

We can use the formula field once we’ve created it. Let’s look at an example:

• Open the Price Books Tab and create a new price book with the fields Revenue and No Of Book.
• After that, click on the Details section, and here the Minimum Price field appears which shows you the result of the MCEILING() function.

As a result, we now know how to use MCEILING() Function Salesforce Classic.

Conclusion

In conclusion, we can say that Salesforce MCEILING() Function is a wonderful and effective approach to round a number up to the nearest integer, or towards zero if it’s negative. We also focused on the MCEILING() Function syntax and several examples from the business world.

Additionally, we went over the step-by-step instructions for using the MCEILING() Function in Salesforce Lightning and Salesforce Classic.

You may like to read:

• MIN() Function in Salesforce | Calculate minimum value in Salesforce
• CEILING() Function in Salesforce