Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
InternIntern 

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.
NPMNPM

Does adding (   ) around the True result help?

IF(ProbabilityFW__c > 75, (ProbabilityFW__c * Opportunity_Amount__c), Opportunity_Amount__c)
EricBEricB
Percentages 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
InternIntern
I 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. 
NPMNPM

This 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! 
InternIntern
Brilliant, it worked perfectly, thank you.
NPMNPM
In 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.
EricBEricB
For 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
NPMNPM
Thanks - that is good information to know, the syntax check gave no indication.