You need to sign in to do that
Don't have an account?
Mission 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))))
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))))
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?
============================================
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)
=============================================
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)
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).