You need to sign in to do that

Don't have an account?

Intern

# IF(ProbabilityFW__c > 75, ProbabilityFW__c * Opportunity_Amount__c, Opportunity_Amount__c)

I have a custom formula field called forecast. My formula for it is: IF(ProbabilityFW__c > 75, ProbabilityFW__c * Opportunity_Amount__c, Opportunity_Amount__c)

What im trying to do is to say that IF my probability field (ProbabilityFW_c) is greater then 75% then the forecast field should show that probability times the Amount (Opportunity_Amount_c). IF the Probability is less then 75% i want the forecast field to equal the Amount (Opportunity_Amount_c).

The problem is that no matter what my (ProbabilityFW_c) equalls above 75% or below i still only get the Amount value and not Amount times Probability for above 75%.

I can not figure out what I am doing wrong.

NPMDoes adding ( ) around the True result help?

IF(ProbabilityFW__c > 75, (ProbabilityFW__c * Opportunity_Amount__c), Opportunity_Amount__c)

EricBPercentages are expressed as decimals in the formula language (100% = 1), so if you want to compare your field to 75%, you should write it as 0.75.

IF(ProbabilityFW__c > 0.75, ProbabilityFW__c * Opportunity_Amount__c, Opportunity_Amount__c)

Hopefully this does the trick!

Eric

InternI tried both suggestions the ( ) and the .75, the .75 gives me a syntax error for the . and i have other formulas where you enter the amount for the percentage as a whole number and it works fine, as for the ( ) there was no syntax error but the formula still did not work. NPMThis will work (using your field names of course).

IF( Probability__c >75/100, (Probability__c * Opportunity_Amount__c) , Opportunity_Amount__c )

I started playing with this in Excel and ultimately realized that percentage formatted fields actually take the entered value and multiply it by 100 (and add the % sign). Thus you need to divide 75 by 100 to compensate. This was tested in my test object and works.

Great learning experience - good luck!

InternBrilliant, it worked perfectly, thank you. NPMIn reality it is almost exactly the same solution EricB suggested, but the syntax does not seem to allow a decimal point in the formula that is why the /100 was needed. EricBFor the record, percentages in formulas do need to be expressed as decimal representations of the display number divided by 100.

100% = 100 / 100 = 1

90% = 90 / 100 = 0.9

15% = 15 / 100 = 0.15

The syntax error you encountered is because the formula parser requires a leading zero in front of the decimal point.

So if you you had used 0.75 instead of .75 in your formula, that would have worked.

Cheers,

Eric Bezar

force.com Platform Product Management

NPMThanks - that is good information to know, the syntax check gave no indication.