Stump the Panel

Paul Grenier, Lead Moderator

Stump the Panel » End Users and Information Workers

1. 

   mtkammerait
   Member

   Alright, this one is admittedly a doozy but I'll be forever indebted if you can help me figure it out. I'm writing a calculated column formula and I need some help. The formula is for an estimate request system I'm building using a custom list. Based on 2-3 different criteria, I'm populating an auto assign column which will decide which of 3 groups needs to do the estimate. Here's the logic I'm trying to encompass and the IF statements I wrote for each one so far.

   IF Periodical, THEN Distribution ----- IF([Class of Mail]=Periodical),"Distribution",

   IF Standard AND Flat, THEN Comail ----- IF(AND([Class of Mail]=Standard,[Rate Type]=Flat)),"Comail",

   IF Standard AND Letter AND >800000, THEN Comingle ----- IF(AND(AND([Class of Mail]=Standard,[Rate Type]=Letter),[Circulation]>800000)),”Comingle”,

   IF Standard AND Letter AND <800000, THEN Distribution ----- IF(AND(AND([Class of Mail]=Standard,[Rate Type]=Letter),[Circulation]<800000)),”Distribution”,

   IF First Class AND Flat, THEN Distribution ----- IF(AND([Class of Mail]=First Class,[Rate Type]=Flat)),”Distribution”,

   IF First Class AND Letter, THEN Comingle ----- IF(AND([Class of Mail]=First Class,[Rate Type]=Letter)),”Comingle”,

   IF Package Services, THEN Distribution ----- IF([Class of Mail]=Package Services),”Distribution”

   So the final formula for the whole system comes to:

   IF([Class of Mail]=Periodical,"Distribution"),IF(AND([Class of Mail]=Standard,[Rate Type]=Flat),"Comail",IF(AND(AND([Class of Mail]=Standard,[Rate Type]=Letter),[Circulation]>800000)),"Comingle",IF(AND(AND([Class of Mail]=Standard,[Rate Type]=Letter),[Circulation]<800000)),"Distribution",IF(AND([Class of Mail]=First Class,[Rate Type]=Flat)),"Distribution",IF(AND([Class of Mail]=First Class,[Rate Type]=Letter)),"Comingle",IF([Class of Mail]=Package Services),"Distribution"

   But (of course) I'm getting a syntax error. If anyone can find my error(s) I will be incredibly greatful.

   A few ideas I have on what it might be: I typed the names of the columns instead of double clicking them in the list (they're identical, but I don't know if it might cause a problem to type them) Also, I'm not sure you can use an AND( within and AND(, does anyone know this? If that's not allowed, do you know another way to have 3 conditions for an IF statement?

   Please keep in mind I'm (unfortunately) working with WSS, not MOSS.

   Thanks in advance for any help anyone might be able to provide.

   Posted 2 days ago #
2. 

   AutoSponge
   Administrator

   You can't have more than 7 nested IF statements. But it looks like you have 7, so we might be ok. Now to check the syntax:

   IF([Class of Mail]=Periodical,"Distribution"),...

   You don't need a comma here unless you're wrapping everything up with another function. Instead, let's use '&'.

   IF([Class of Mail]=Periodical,"Distribution")&...

   Next, you can combine some of these but we might want to edit it later, so it's easier if we don't (like IF Periodical OR Package Services, THEN Distribution).

   However, we can rewrite the branching IF statements to make them easier to manage:

   IF([Class of Mail]="Standard",
   	IF([Rate Type]="Flat","Comail")&
   	IF([Rate Type]="Letter",
   		IF([Circulation]>800000,
   			"Comingle","Distribution"
   		)
   	)
   )&
   IF([Class of Mail]="First Class",
   	IF([Rate Type]="Flat","Distribution")&
   	IF([Rate Type]="Letter","Comingle")
   )&
   IF([Class of Mail]="Package Services",
   	"Distribution")

   Let's walk through the logic:

   1. IF [Class of Mail]... If we pick any value for this, we should get only one path to follow. So, our & (concat) will not return "DistributionDistribution".
   2. After choosing a path, we look at Rate Type. Again, we can use & because we only allow one choice per branch.
   3. Inside Rate Type, we check Circulation (which we cheat on at the end by using the 'else' condition after our IF-TRUE result.

   We can repeat this pattern as needed.

   When we flatten it out, it looks like this:

   IF([Class of Mail]="Standard",IF([Rate Type]="Flat","Comail")&IF([Rate Type]="Letter",IF([Circulation]>800000,"Comingle","Distribution")))&IF([Class of Mail]="First Class",IF([Rate Type]="Flat","Distribution")&IF([Rate Type]="Letter","Comingle"))&IF([Class of Mail]="Package Services","Distribution")

   Try that out and see if it helps.

   Posted 2 days ago #

RSS feed for this topic

Reply

You must log in to post.