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
miss vmiss v 

Custom Formula using IF, AND?

I need to write a formula that calcualtes payroll cycles for each of our Accounts.  I have already written the formula below that works, but i need to write in an exception for one Account that basically says the same thing below, but changes the result number if the ACCOUNT= TEST.  

IF(ISPICKVAL(Payroll_Cycle__c, "Weekly"), 52,
IF( ISPICKVAL(Payroll_Cycle__c, "Semi Monthly"), 24,
IF( ISPICKVAL(Payroll_Cycle__c, "Monthly"), 12,
IF( ISPICKVAL(Payroll_Cycle__c, "Biweekly"), 26, null
)
)
)
)


I tried to add this in, but it still calculates Biweekly as 26 instead of 24: IF(AND(ISPICKVAL(Payroll_Cycle__c, "Biweekly"), Account__c: ="Test"), 24,

Does anyone have any suggestions?


Message Edited by miss v on 04-14-2008 07:38 AM

Message Edited by miss v on 04-14-2008 07:44 AM
NPMNPM
I think when using IF that when the formula is evaluated as soon as the 1st True condition is found the rest of the formula is not evaluated.  You may want to try moving your exception to the top.
miss vmiss v
Well, now I've been able to write it correctly and it saves without syntax errors, but it doesn't produce the correct results - I've tried reordering the exceptions:

IF(ISPICKVAL(Payroll_Cycle__c, "Weekly"), 52,
IF( ISPICKVAL(Payroll_Cycle__c, "Semi Monthly"), 24,
IF(AND(ISPICKVAL(Payroll_Cycle__c, "Biweekly"), Account__c ="AIMCO"), 24,
IF(AND(ISPICKVAL(Payroll_Cycle__c, "Biweekly"), NOT(Account__c ="AIMCO")), 26,
IF( ISPICKVAL(Payroll_Cycle__c, "Monthly"), 12, null
)
)
)
)
)

It always calculates 26 for Biweekly - ARGH! 


NPMNPM

Just for troubleshooting would you take out the line to see if 24 is possible? ,also confirm that Account__c is a text field that is a duplicate of the Account Name field:

IF(AND(ISPICKVAL(Payroll_Cycle__c, "Biweekly"), NOT(Account__c ="AIMCO")), 26,

miss vmiss v
The Account__c field is a lookup for the master-detail relationship - it is the only Account field that custom object. 

However, you are right, when I removed the '26' line, this line failed to populate 24 - so there is something wrong with the way it is written.
IF(AND(ISPICKVAL(Payroll_Cycle__c, "Biweekly"), Account__c ="AIMCO"), 24,

 Any insight?


 
NPMNPM

 

Just noticed - Is there a missing ")" ?

IF(AND(ISPICKVAL(Payroll_Cycle__c, "Biweekly"), Account__c ="AIMCO"), 24,

I count three "(" and  two ")"

IF(AND(ISPICKVAL(Payroll_Cycle__c, "Biweekly"), Account__c ="AIMCO")), 24,
miss vmiss v
If you look at the original formula, the ")" 's are all accounted for...  I was just copying and pasting the one line to keep these posts clean...

It's definitely not a syntax error - it has to do with the formula expressions not being read correctly for some reason.  I just can't figure it out.  :(
NPMNPM

I'm not really surprised that that was not it, I felt pretty confident from your previous posts that it was a copy and paste error, but I was grasping at straws.

I'm puzzled, but will keep thinking about this.

 

 

   
miss vmiss v
Thanks for your help  - I was able to solve it...  because it was a look up, i had to use the Account ID instead of the text...  I'm posting it just in case it helps someone else.   Again, I really appreciate you talking me through it. ; )

IF(ISPICKVAL(Payroll_Cycle__c, "Weekly"), 52,
IF( ISPICKVAL(Payroll_Cycle__c, "Semi Monthly"), 24,
IF(AND(ISPICKVAL(Payroll_Cycle__c, "Biweekly"), Account__c="0013000000DetVh"), 24,
IF( ISPICKVAL(Payroll_Cycle__c, "Biweekly"), 26,
IF( ISPICKVAL(Payroll_Cycle__c, "Monthly"), 12, null
)
)
)
)
)
NPMNPM

Yipeee!

 

Thanks:smileyvery-happy: