Starting November 20, the site will be set to read-only.
On December 4, 2023,
Apex Code Development (90768)
General Development (55146)
Visualforce Development (37251)
Lightning (18265)
APIs and Integration (17146)
Trailhead (11680)
Formulas & Validation Rules Discussion (11337)
Other Salesforce Applications (8116)
Jobs Board (6655)
Force.com Sites & Site.com (4842)
Mobile (2694)
You need to sign in to do that
Don't have an account?
dhart@sayers.com On the opportunity page I would like to take the value of one field (Revenue) then subtract the value of another field (Gross Profit). My new field will be Margin percentage and would be the Revenue minus Gross Profit divided by Revenue minus one. What is the formula I need to add.
Assuming your field names are Revenue__c and Gross_Profit__c then your formula would be
(Revenue__c - Gross_Profit__c) / (Revenue__c -1)
Make sure you select the Advanced Formula Tab, and set the value for how to deal with blank fields.
Excellent point... you will want to treat blanks as zeros for this formula.
That leads to the question, what does Salesforce do when it divides by 0?
Would it be better if the formula included an if statement to rule out blanks? Such as:
if(or(isblank(Revenue__c),isblank(Gross_Profit__c)),"",(Revenue__c - Gross_Profit__c) / (Revenue__c -1))
This would only put a value in the field if there were a value in both the gross profit and the revenue and avoid the taboo of dividing by 0 until there is actually a value to put in.
Divide by Zero would result in #Error being displayed.
Your if statement is generally how you would want to handle this, however since you aren't including Gross_Profit__c in your divisor you don't need to check for it in your condition. You do need to check if Revenue -1 = 0. Since you are treating blanks as 0, then a blank wouldn't actually result in a division by zero.
I think you would want something like:
IF(Revenue-1 = 0, "" , (Revenue__c - Gross_Profit__c) / (Revenue__c -1))
@Kyle.T is right, at the end of the day you're still governed by the laws of mathematics, so diving by 0 will still return #Error, unless you either throw in IF statement that addresses the 0 ahead of time, or better yet add VR's to all of the fields that are feeding your formula making 0 and blank invalid.
Kyle, thank you very much for the formula. When I am inputing the suggested formula syntax errors are coming up.
My intent is to get a Gross Profit Percentage in field value Margin %
This calculation would come from (field values) Amount minus Revenue equals *** then divide this number *** by Amount field minus 1.
Can you confirm the formula once again:
if(or(isblank(Revenue__c),isblank(Amount)),"",(Revenue__c - Amount) / (Revenue__c -1))
Again, I do appreciate your help with this.
Remeber that you should set the formula to treat blanks as 0 so you don't need to check if the field is blank. What you need to check for is that Revenue__c - 1<> 0.
IF(Revenue__c - 1 = 0,"", (Revenue__c - Amount) / (Revenue__c -1))
This basically says, revenue minus one equals zero then just return null, otherwise, I can divide by Revenue minus one so return my calculated value.
Kyle, thanks you again. Still receiving an error "Error: Field Revenue__c may not be used in this type of formula" What am I doining wrong?
I am assuming that Revenue__c is a Currency field? Is that not the case?
It is a currency field
I couldn't reproduce the error but did make one modification
IF(Revenue__c - 1 = 0,0, (Revenue__c - Amount) / (Revenue__c -1))
No I receive the follwoing:
Error: Formula result is data type (Number), incompatible with expected data type (true or false).
You need to change the return type of the formula from boolean to Currency.
Kyle, I apologize I do not know what you mean to chenge from boolean to Currency.
@ jamndav
You need to create a new custom field and specify the the Datatype = Currency and then insert @kyle.t's formula. There error message you're getting is because you are trying to insert the Formula into the Defualt Value Formula box on a regular Currency field.
You cannot change the datatype of a field from Currency, Number, Text, Date, etc to a Formula(Currency, Number, Text, Date, etc.) you have to specify that the field is a Formula (or not) when you create it, after that there is no "undo"
When you are editing the formula there should be a drop down field called "Formula Return Type" Just under the Formula Options section header. It should be currency in this case.
This is where you need to be:
but I have a feeling this is where you're trying to enter @kyle.t's formula (and that won't work)
I do not have a formula check box.
Data Type
Auto Number
A system-generated sequence number that uses a display format you define. The number is automatically incremented for each new record.
Checkbox
Allows users to select a True (checked) or False (unchecked) value.
Currency
Allows users to enter a dollar or other currency amount and automatically formats the field as a currency amount. This can be useful if you export data to Excel or another spreadsheet.
Date
Allows users to enter a date or pick a date from a popup calendar.
Date/Time
Allows users to enter a date and time, or pick a date from a popup calendar. When users click a date in the popup, that date and the current time are entered into the Date/Time field.
Email
Allows users to enter an email address, which is validated to ensure proper format. If this field is specified for a contact or lead, users can choose the address when clicking Send an Email. Note that custom email addresses cannot be used for mass emails.
Number
Allows users to enter any number. Leading zeros are removed.
Percent
Allows users to enter a percentage number, for example, '10' and automatically adds the percent sign to the number.
Phone
Allows users to enter any phone number. Automatically formats it as a phone number.
Picklist
Allows users to select a value from a list you define.
Picklist (Multi-Select)
Allows users to select multiple values from a list you define.
Text
Allows users to enter any combination of letters and numbers.
Also, I want to do all of this on the opportunity page not the case page.
*sigh* You're killing me, like a cigarette... in a very slow, almost passive way, you're killing me...
That was just a screenshot of a Formula Field, it doesn't matter what SFDC Object you're trying to create it on the steaps are the same.
https://na1.salesforce.com/help/doc/en/adding_fields.htm
The point is that you have to create a NEW custom formula field, you cannot insert a formula into an existing field that was not a Formula Datatype when it was created. The error message that you're getting is because you are trying to shove an Advanced Formula into the Default Value Box on a field that is not a Datatype Formula.
Ok, I think I have it working. I logged into our Sandbox account and established the field and parameters and it is working.
But the calculation is working backwards. Example = it displays 96% when it should be 4%. Apologies and I know I am killing you but I am new to working with this application and sincerely appreciate your help.
I fixed it by changing the formula to IF(Revenue__c - 1 = 0,0, (Amount) / (Revenue__c -1 ))
A BIG THANKS TO YOU.
F(Revenue__c - 1 = 0,0, 1 - ((Revenue__c - Amount) / (Revenue__c -1)))
No problem @jamndav half the time I'm kidding and the other half I have no idea what I am talking about ;-) Kudos to @kyle.t (he did 99% of the coding) I just grabbed the rebound and hit the lay-up.