function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

Error on IF Formula is Unclear

I am trying to create a complex nested IF formula, but I keep getting an error 'invalid parameters'.  Can anyone see what is incorrect?

 

IF (ISPICKVAL(StageName, "Closed Won"),
IF( Qualifier__c <> OwnerId ,
IF(
Users__c<20,150,
IF(
Users__c<50,300,
IF(
Users__c<100,750,
IF(
Users__c>=100,1500,0
)
)
)
)
),
0
)

 

 

Best Answer chosen by Admin (Salesforce Developers) 
EtaussigEtaussig

OK.  This is probably the completely wrong way to do this, but it worked for me.

 

Step 1:  create a text formula called test__c.

 

IF(AND(Qualifier__c<>OwnerId, ISPICKVAL(StageName, "Closed Won")),"YES", "NO")

 

Save this.

 

Step 2:  create a number formula (whatever name you are calling this field):

IF(AND(TEST__c="YES", Users__c<20, 150,

IF(AND(TEST__c="YES", Users__c<50, 300,

IF(AND(TEST__c="YES", Users__c<100, 750,

IF(AND(TEST__c="YES", Users__c>=100, 1500,0))))

All Answers

EtaussigEtaussig
I think you need to add "AND" to your formula.
DRPowerUserDRPowerUser

I inserted the "AND" at the beginning of the formula per SFDC instructions.  I'm still getting various error messages (e.g. Extra ",").

 

Here's my latest formula.

 

IF (AND (ISPICKVAL(StageName, "Closed Won"), Qualifier__c <> OwnerId ),
IF(
Users__c<20,150,
IF(
Users__c<50,300,
IF(
Users__c<100,750,
IF(
Users__c>=100,1500,0
)
)
)
)
)
,
0)
EtaussigEtaussig
Is Users__c a picklist value or just a value entered?
DRPowerUserDRPowerUser
The Users__c field is a value field where numbers from 1-200 are entered.  It isn't a picklist field.
Siddhesh KabeSiddhesh Kabe

IF(ISPICKVAL(StageName, "Closed Won":smileywink:, Qualifier__c <> OwnerId ),IF(Users__c<20,150, IF(Users__c<50,300,IF(Users__c<100,750,IF(Users__c>=100,1500,0)))))

 

try this...not sure if it will work...but neverthe less...give it a shot

DRPowerUserDRPowerUser

Still no luck.  Here is the error message I get

 

"Error:  Incorrect number of parameters for function ISPICKVAL().  Expected 2, received 3.
EtaussigEtaussig

Before you get to the correct formula, I see an error in your logic.  If Users__c<20, then it is also less than 50, 100, etc.  What number should it select?  I think you need to build it more like if users__c<20, 150, if users__c>20 and <50, 300...

 

I'll try to think about the formula a liite more.
DRPowerUserDRPowerUser

I currently use that logic in a different field and it all works fine since if users are not <20, it goes to the next IF statement until it finds a match based on criteria.

 

The only complication I'm having is when I try to use 2 "AND" filters before I get to the IF for users.

 

Thanks for your help.
EtaussigEtaussig

OK.  This is probably the completely wrong way to do this, but it worked for me.

 

Step 1:  create a text formula called test__c.

 

IF(AND(Qualifier__c<>OwnerId, ISPICKVAL(StageName, "Closed Won")),"YES", "NO")

 

Save this.

 

Step 2:  create a number formula (whatever name you are calling this field):

IF(AND(TEST__c="YES", Users__c<20, 150,

IF(AND(TEST__c="YES", Users__c<50, 300,

IF(AND(TEST__c="YES", Users__c<100, 750,

IF(AND(TEST__c="YES", Users__c>=100, 1500,0))))

This was selected as the best answer
EtaussigEtaussig
DRPowerUser:  Did the formulas work?
DRPowerUserDRPowerUser

Unfortunately, I needed to use the existing fields.  What is strange is that both sets of formulas work properly (see previous message), but when I try to combine the 2 statements with "AND" it breaks.  It shouldn't be affected by the Users__c field since it already works.

 

 
SteveBowerSteveBower

Wait... you still don't have this? 

 

Post your latest attempt and the error message.   And verify that this Formula is for a field on the Opportunity object (because I presume that's where the "StageName" is coming from), and that the other fields a) exist b) are of the correct type (Id, numeric, etc.), and c) you have access to those fields.

 

Best, Steve.

DRPowerUserDRPowerUser

Thanks for the assistance.  It is a strange issue since I can use each of the formula elements in their own formula and it works fine.  However, when I try to incorporate the 'AND' so that StageName = Closed Won and that Owner of Oppty is not the same as Qualifier it doesn't work. 

 

Here is the exact formula I uploaded and the error message:

 

IF (AND (ISPICKVAL(StageName, "Closed Won") Qualifier__c<> OwnerId,
IF(
Users__c<20,150,
IF(
Users__c<50,300,
IF(
Users__c<100,750,
IF(
Users__c>=100,1500,0))))),0)

 

Error message:  Syntax error.  Missing ')'
SteveBowerSteveBower

I believe this is what you want below.  E.G. if the stage is closed_won, and the Qualifer is not the OwnerId, then use the chain of If statements to compute the result based on the Users_c value.

 

The problem you're having isn't that of unbalanced parenthesis, but rather misplaced or missing commas.   Specifically, look at the syntax of the AND statment.  It's   AND(logical1, logical2).   You're missing the comma just before Qualifier__c.   From  there, things go downhill.

 

Best, Steve.

 

 

IF (
AND (
ISPICKVAL(StageName, "Closed Won") ,
Qualifier__c<> OwnerId
),
IF( Users__c<20,150,
IF( Users__c<50,300,
IF( Users__c<100,750,
IF( Users__c>=100,1500,0)
)
)
),
0)
 
 
 


DRPowerUserDRPowerUser
Steve,
 
This worked like a charm.  Thanks for the help it really means a lot that you were able to help.  I really appreciate it.
 
Have a good afternoon.