Skip to main content
Topics#Data ManagementPergunta de Erica Farber
Erica Farber (Balentine) perguntou em #Data Management
29 de abr. de 2015, 17:54

Nested If Statement Parameter Issue!

I am officially stumped (and ready to throw my computer out of the window).

 

 

 

GOAL

 

 

 

Calculate “Projected Annual Fee” based on Fee Schedule, Amount and Discount.

 

 

 

ISSUE

 

There should be 3 ‘IF” Parameters, but my current formula is only providing 2. The specific error message is this: Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2

 

 

 

REQUEST

 

Don’t worry about the math involved in the formula. I just need to clearly show:

 

IF Fee Schedule = Flat, do this.

 

IF Fee Schedule = Tiered (Before 2015 Financial Planning), do this.

 

IF Fee Schedule = Tiered (After 2015 Financial Planning), do this.

 

Anything else? Return 0.

 

Thank you!

 

 

 

FORMULA

 

 

 

IF((TEXT( Fee_Schedule__c) = "Flat"), Amount * ( Flat_Fee_Pct__c * (1 - Discount__c )),

 

 

 

IF((TEXT( Fee_Schedule__c) = "Tiered (Before 2015 Financial Planning)"),

 

IF(Amount <= 2000000, Amount *12.5/1000 * (1 - Discount__c ),

 

IF(Amount <= 4000000, (2000000*12.5/1000* (1 - Discount__c )) + (((Amount - 2000000) * 7/1000* (1 - Discount__c ))),

 

IF(Amount <= 10000000, (2000000*12.5/1000* (1 - Discount__c )) + (2000000*7/1000* (1 - Discount__c )) + (((Amount - 4000000)*6/1000* (1 - Discount__c ))),

 

IF(Amount <= 25000000, (2000000*12.5/1000* (1 - Discount__c )) + (2000000*7/1000* (1 - Discount__c )) + (6000000*6/1000* (1 - Discount__c)) + (((Amount - 10000000) * 4.5/1000)),

 

IF(Amount <= 50000000, (2000000*12.5/1000* (1 - Discount__c )) + (2000000*7/1000* (1 - Discount__c )) + (6000000*6/1000* (1 - Discount__c )) + (15000000*4.5/1000* (1 - Discount__c)) + (((Amount - 25000000) * 3/1000* (1 - Discount__c ))),

 

IF(Amount <= 100000000, (2000000*12.5/1000* (1 - Discount__c )) + (2000000*7/1000* (1 - Discount__c )) + (6000000*6/1000* (1 - Discount__c )) + (15000000*4.5/1000* (1 - Discount__c )) + (25000000*3/1000* (1 - Discount__c)) + (((Amount - 50000000) * 2/1000* (1 - Discount__c ))),

 

IF(Amount <= 250000000, (2000000*12.5/1000* (1 - Discount__c )) + (2000000*7/1000* (1 - Discount__c )) + (6000000*6/1000* (1 - Discount__c )) + (15000000*4.5/1000* (1 - Discount__c )) + (25000000*3/1000* (1 - Discount__c )) + (25000000*2/1000* (1 - Discount__c)) + (((Amount -75000000) * 1/1000* (1 - Discount__c ))),

 

IF(Amount > 250000000, (2000000*12.5/1000* (1 - Discount__c )) + (2000000*7/1000* (1 - Discount__c )) + (6000000*6/10000* (1 - Discount__c )) + (15000000*4.5/1000* (1 - Discount__c )) + (25000000*3/1000* (1 - Discount__c )) + (25000000*2/1000* (1 - Discount__c )) + (((Amount -250000000) * 0.5/1000* (1 - Discount__c ))),

 

 

 

IF((TEXT( Fee_Schedule__c) = "Tiered (After 2015 Financial Planning)"),

 

IF(Amount <= 2500000, Amount *12.5/1000 * (1 - Discount__c ),

 

IF(Amount <= 5000000, (2500000*12.5/1000* (1 - Discount__c )) + (((Amount - 2500000) * 7.5/1000* (1 - Discount__c ))),

 

IF(Amount <= 10000000, (2500000*12.5/1000* (1 - Discount__c )) + (2500000*7.5/1000* (1 - Discount__c )) + (((Amount - 5000000)*6/1000* (1 - Discount__c ))),

 

IF(Amount <= 25000000, (2500000*12.5/1000* (1 - Discount__c )) + (2500000*7.5/1000* (1 - Discount__c )) + (5000000*6/1000* (1 - Discount__c)) + (((Amount - 10000000) * 4.5/1000* (1 - Discount__c))),

 

IF(Amount <= 50000000, (2500000*12.5/1000* (1 - Discount__c )) + (2500000*7.5/1000* (1 - Discount__c )) + (5000000*6/1000* (1 - Discount__c )) + (15000000*4.5/1000* (1 - Discount__c)) + (((Amount - 25000000) * 3/1000* (1 - Discount__c ))),

 

IF(Amount <= 100000000, (2500000*12.5/1000* (1 - Discount__c )) + (2500000*7.5/1000* (1 - Discount__c )) + (5000000*6/1000* (1 - Discount__c )) + (15000000*4.5/1000* (1 - Discount__c )) + (25000000*3/1000* (1 - Discount__c)) + (((Amount - 50000000) * 2/1000* (1 - Discount__c ))),

 

IF(Amount <= 250000000, (2500000*12.5/1000* (1 - Discount__c )) + (2500000*7.5/1000* (1 - Discount__c )) + (5000000*6/1000* (1 - Discount__c )) + (15000000*4.5/1000* (1 - Discount__c )) + (25000000*3/1000* (1 - Discount__c )) + (25000000*2/1000* (1 - Discount__c)) + (((Amount -75000000) * 1/1000* (1 - Discount__c ))),

 

IF(Amount > 250000000, (2500000*12.5/1000* (1 - Discount__c )) + (2500000*7.5/1000* (1 - Discount__c )) + (5000000*6/10000* (1 - Discount__c )) + (15000000*4.5/1000* (1 - Discount__c )) + (25000000*3/1000* (1 - Discount__c )) + (25000000*2/1000* (1 - Discount__c )) + (((Amount -250000000) * 0.5/1000* (1 - Discount__c )))),

 

0

 

))))))))))))))))))
9 respostas
  1. Erica Farber (Balentine)
    30 de abr. de 2015, 03:15
    So I replaced my IF Formula with CASE and received the same message. Unfortunately (for me), that means something else is wrong. Specifically, I don't think I've properly used my parenthesis to indicate where my third parameter begins. I'll gladly take any suggestions, and your support is awesome!
Escrever uma resposta...
0/9000