ShowAll Questionssorted byDate Posted
Sebastien Raoux

# Formula - result different than excel

Hi

i have a formula that i implemented in Salesforce , but the result is not the same as in Excel
formula in excel is : Tier = [0.72 * IF( NbrEmp>120000,1,IF(NbrEmp<65000,3,2)) + 0.75 * IF(MCap>72,1,IF(MCap<26,3,2)) + 0.56 * IF(CA>57,1,IF(CA<20,3,2)) ] /2.03

i replaced some variable by the field name in SF so in SF it s
(0.72 * IF( NumberOfEmployees >120000,1,IF( NumberOfEmployees <65000,3,2)) + 0.75 * IF( Market_Capitalization__c >72000,1,IF( Market_Capitalization__c <26000,3,2)) + 0.55 * IF( Revenue__c >56000,1,IF(Revenue__c<20000,3,2))  ) /2.03

but the result of the operation i have is different in Excel and SF ...

is there any explication to that ?
Sebastien Raoux
just want to add there is only decimal difference ut it makes a massive difference for the number i want to have .

for exemple in excel i can have 1,65 as a result but in SF it will be 1,76

Alain Cabon
You should give all the values of NbrEmp (NumberOfEmployees), MCap (Market_Capitalization__c) and CA (Revenue__c) for the given results 1,65 and 1,76 and their type.
There are many conditions in your formula.

Tier = [0.72 * IF( NbrEmp>120000,1,IF(NbrEmp<65000,3,2)) + 0.75 * IF(MCap>72,1,IF(MCap<26,3,2)) + 0.56 * IF(CA>57,1,IF(CA<20,3,2)) ] /2.03

(0.72 * IF( NumberOfEmployees >120000,1,IF( NumberOfEmployees <65000,3,2)) + 0.75 * IF( Market_Capitalization__c >72000,1,IF( Market_Capitalization__c <26000,3,2)) + 0.55 * IF( Revenue__c >56000,1,IF(Revenue__c<20000,3,2))  ) /2.03

There is 0.56 in a formula and 0.55 in the other formula ( typo ?)