Stump the Panel Topic: Calculated Column Formula

Stump the Panel Topic: Calculated Column Formula - Challenge! http://www.endusersharepoint.com/STP/ Paul Grenier, Lead Moderator en Tue, 10 Feb 2009 10:37:07 +0000 AutoSponge on "Calculated Column Formula - Challenge!" http://www.endusersharepoint.com/STP/topic/calculated-column-formula-challenge#post-4059 Mon, 09 Feb 2009 22:21:32 +0000 AutoSponge 4059@http://www.endusersharepoint.com/STP/ mtkammerait, the construction is <condition>,true,false so everywhere we have a "dangling false" we can place "" and that should do it. Try this: <pre><code>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","")</code></pre> mtkammerait on "Calculated Column Formula - Challenge!" http://www.endusersharepoint.com/STP/topic/calculated-column-formula-challenge#post-4038 Mon, 09 Feb 2009 11:46:08 +0000 mtkammerait 4038@http://www.endusersharepoint.com/STP/ First of all... Thank you, Thank you, Thank you! You've gotten me much closer. However, I've hit another snag. With slight modification of the formula you provided, I can get an output that CONTAINS the output I need. However, it also contains FALSE for each if statement that returns false. So, if the desired output is distribution, I'm getting FALSEFALSEDistribution or FALSEDistributionFALSEFALSE, or something to that effect for each one. is there a way to have the output set to nothing rather than FALSE if the output comes back FALSE? That way my output would be just Comingle, Distribution, Comail, etc. Or, is there a way to use another column to "clean up" this column, I've been trying this but can't seem to find a way to write a column that says "If this column CONTAINS Distribution, to give the output "Distribution" or if this column CONTAINS Comail, to give the output "Comail." Any ideas? AutoSponge on "Calculated Column Formula - Challenge!" http://www.endusersharepoint.com/STP/topic/calculated-column-formula-challenge#post-4024 Fri, 06 Feb 2009 19:17:28 +0000 AutoSponge 4024@http://www.endusersharepoint.com/STP/ 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: <code>IF([Class of Mail]=Periodical,"Distribution"),...</code> You don't need a comma here unless you're wrapping everything up with another function. Instead, let's use '&'. <code>IF([Class of Mail]=Periodical,"Distribution")&...</code> 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: <pre><code>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")</code></pre> 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: <code>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")</code> Try that out and see if it helps. mtkammerait on "Calculated Column Formula - Challenge!" http://www.endusersharepoint.com/STP/topic/calculated-column-formula-challenge#post-4015 Fri, 06 Feb 2009 14:38:01 +0000 mtkammerait 4015@http://www.endusersharepoint.com/STP/ 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.