function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Mission Teams DepartmentMission Teams Department 

Formula not calculating properly

Hello,
I am trying to create a formula to calculate a cost for if a headcount is less than or equal to 10 and if a headcount is greater than 10. Below is the formula that has been created. The formula will calculate the first half (if the headcount is more than 10), but not the second half (if the headcount is less than 10). Any help is appreciated. 

IF(ISPICKVAL(Housing__c, "Kansas City - Hope Faith") || ISPICKVAL(Housing__c, "Kansas City - Bread of Life ($10.00)") || ISPICKVAL(Housing__c, "Kansas City - Sanctuary of Hope") || ISPICKVAL(Housing__c, "Des Moines - Luther Memorial") || ISPICKVAL(Housing__c, "Omaha- Faith Westwood") || ISPICKVAL(Housing__c, "Omaha- St. Andrews UMC") || ISPICKVAL(Housing__c, "Las Vegas - YWAM")|| ISPICKVAL(Housing__c, "Las Vegas - Barry Glaser’s house")|| ISPICKVAL(Housing__c, "Phoenix Dream Center") && (NumberSent>10) ,10.00 * UP_Auto_Number_of_Nights__c *  NumberSent,

IF(ISPICKVAL(Housing__c, "Kansas City - Hope Faith") || ISPICKVAL(Housing__c, "Kansas City - Bread of Life ($10.00)") || ISPICKVAL(Housing__c, "Kansas City - Sanctuary of Hope") || ISPICKVAL(Housing__c, "Des Moines - Luther Memorial") || ISPICKVAL(Housing__c, "Omaha- Faith Westwood") || ISPICKVAL(Housing__c, "Omaha- St. Andrews UMC") || ISPICKVAL(Housing__c, "Las Vegas - YWAM")|| ISPICKVAL(Housing__c, "Las Vegas - Barry Glaser’s house")|| ISPICKVAL(Housing__c, "Phoenix Dream Center") && (NumberSent<=10) ,10.00 * UP_Auto_Number_of_Nights__c *  10,

IF (ISPICKVAL(Housing__c, "Omaha - Better Together Campus"), 10.00*UP_Auto_Number_of_Nights__c *NumberSent,

IF (ISPICKVAL(Housing__c, "Dallas - Cornerstone Baptist"), 10.00*UP_Auto_Number_of_Nights__c * NumberSent,0))))
Alain CabonAlain Cabon
=========================================
                           OLD FORMULA ?
=========================================

IF( (
   ISPICKVAL(Housing__c, "Kansas City - Hope Faith") ||
   ISPICKVAL(Housing__c, "Kansas City - Bread of Life ($10.00)") ||
   ISPICKVAL(Housing__c, "Kansas City - Sanctuary of Hope") ||
   ISPICKVAL(Housing__c, "Des Moines - Luther Memorial") ||
   ISPICKVAL(Housing__c, "Omaha- Faith Westwood") ||
   ISPICKVAL(Housing__c, "Omaha- St. Andrews UMC") ||
   ISPICKVAL(Housing__c, "Las Vegas - YWAM")||
   ISPICKVAL(Housing__c, "Las Vegas - Barry Glaser’s house")||
   ISPICKVAL(Housing__c, "Phoenix Dream Center")
  )
    && (NumberSent>10) ,10.00 * UP_Auto_Number_of_Nights__c *  NumberSent,

IF( (
   ISPICKVAL(Housing__c, "Kansas City - Hope Faith") ||
   ISPICKVAL(Housing__c, "Kansas City - Bread of Life ($10.00)") ||
   ISPICKVAL(Housing__c, "Kansas City - Sanctuary of Hope") ||
   ISPICKVAL(Housing__c, "Des Moines - Luther Memorial") ||
   ISPICKVAL(Housing__c, "Omaha- Faith Westwood") ||
   ISPICKVAL(Housing__c, "Omaha- St. Andrews UMC") ||
   ISPICKVAL(Housing__c, "Las Vegas - YWAM")||
   ISPICKVAL(Housing__c, "Las Vegas - Barry Glaser’s house")||
   ISPICKVAL(Housing__c, "Phoenix Dream Center")
)
     && (NumberSent<=10) ,10.00 * UP_Auto_Number_of_Nights__c *  10,

IF (ISPICKVAL(Housing__c, "Omaha - Better Together Campus"), 10.00*UP_Auto_Number_of_Nights__c *NumberSent,

IF (ISPICKVAL(Housing__c, "Dallas - Cornerstone Baptist"), 10.00*UP_Auto_Number_of_Nights__c * NumberSent,0))))

===============================
           NEW FORMULA (simpler)
===============================

IF( (
   ISPICKVAL(Housing__c, "Kansas City - Hope Faith") ||
   ISPICKVAL(Housing__c, "Kansas City - Bread of Life ($10.00)") ||
   ISPICKVAL(Housing__c, "Kansas City - Sanctuary of Hope") ||
   ISPICKVAL(Housing__c, "Des Moines - Luther Memorial") ||
   ISPICKVAL(Housing__c, "Omaha- Faith Westwood") ||
   ISPICKVAL(Housing__c, "Omaha- St. Andrews UMC") ||
   ISPICKVAL(Housing__c, "Las Vegas - YWAM")||
   ISPICKVAL(Housing__c, "Las Vegas - Barry Glaser’s house")||
   ISPICKVAL(Housing__c, "Phoenix Dream Center")
  ),
  IF  ((NumberSent>10) ,10.00 * UP_Auto_Number_of_Nights__c *  NumberSent,
    
    IF ((NumberSent<=10) ,10.00 * UP_Auto_Number_of_Nights__c *  10,

       IF (ISPICKVAL(Housing__c, "Omaha - Better Together Campus"), 10.00*UP_Auto_Number_of_Nights__c *NumberSent,

          IF (ISPICKVAL(Housing__c, "Dallas - Cornerstone Baptist"), 10.00*UP_Auto_Number_of_Nights__c * NumberSent,0),0),0),0),0)

========================================

NumberSent<=10 = useless?
Mission Teams DepartmentMission Teams Department
Thank you for your suggestion on the simpler formula! I tried using the formula you suggested, but received this error message when checking the syntax " Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 4" My apologizes if this is a simple fix, I am still learning how to use formulas in Salesforce. Unfortunately, NumberSent
Alain CabonAlain Cabon
Fixed.
============================================

IF( ( 
ISPICKVAL(Housing__c, "Kansas City - Hope Faith") || 
ISPICKVAL(Housing__c, "Kansas City - Bread of Life ($10.00)") || 
ISPICKVAL(Housing__c, "Kansas City - Sanctuary of Hope") || 
ISPICKVAL(Housing__c, "Des Moines - Luther Memorial") || 
ISPICKVAL(Housing__c, "Omaha- Faith Westwood") || 
ISPICKVAL(Housing__c, "Omaha- St. Andrews UMC") || 
ISPICKVAL(Housing__c, "Las Vegas - YWAM")|| 
ISPICKVAL(Housing__c, "Las Vegas - Barry Glaser’s house")|| 
ISPICKVAL(Housing__c, "Phoenix Dream Center") 
), 
IF ((NumberSent__c >10) ,10.00 * UP_Auto_Number_of_Nights__c * NumberSent__c, 

IF ((NumberSent__c <=10) ,10.00 * UP_Auto_Number_of_Nights__c * 10, 

IF (ISPICKVAL(Housing__c, "Omaha - Better Together Campus"), 10.00*UP_Auto_Number_of_Nights__c *NumberSent__c, 

IF (ISPICKVAL(Housing__c, "Dallas - Cornerstone Baptist"), 10.00*UP_Auto_Number_of_Nights__c * NumberSent__c,0)))),0)

=============================================
 
Alain CabonAlain Cabon
Shorter formula using contains. For picklist, we use simply TEXT instead of ISPICKVAL.

IF( (contains( 
"Kansas City - Hope Faith|Kansas City - Bread of Life ($10.00)|Kansas City - Sanctuary of Hope|Des Moines - Luther Memorial|Omaha- Faith Westwood|Omaha- St. Andrews UMC|Las Vegas - YWAM|Las Vegas - Barry Glaser’s house|Phoenix Dream Center", TEXT(Housing__c) ) 
), 

IF ((NumberSent__c >10) ,10.00 * UP_Auto_Number_of_Nights__c * NumberSent__c, 

IF ((NumberSent__c <=10) ,10.00 * UP_Auto_Number_of_Nights__c * 10, 

IF (ISPICKVAL(Housing__c, "Omaha - Better Together Campus"), 10.00*UP_Auto_Number_of_Nights__c *NumberSent__c, 

IF (ISPICKVAL(Housing__c, "Dallas - Cornerstone Baptist"), 10.00*UP_Auto_Number_of_Nights__c * NumberSent__c,0)))),0)

 
Mission Teams DepartmentMission Teams Department
Thank you for the updated formula! I tested it out, but now the "Dallas-Cornerstone Baptist" pick value is not working. I tested out a trip and it is not calculating a cost. For example, the number sent is 0 and the UP_Auto_Number_of_Nights is 2. So it should be calculating 10*2*5. What is showing up is 0. Is it because there is an extra 0 at the end of the formula 0))))*,0*)? The first 0 closes the formula, right? Thanks for any updated suggestions.
Alain CabonAlain Cabon
Hello,

Could you clarify the rules of your formula in plain english?

I made technical changes (simplify) but I don't know the specifications at all (for me, it is just a working formula,right or wrong? I can't know).
 
Mission Teams DepartmentMission Teams Department
I am so sorry for the delayed response! Thank you so much for your help with the formula. We have figured out a solution. Thank you again for your time and energy you put into helping us! Blessings, Rachel