How to use IF

aharritt

This might be simple, but I am a beginner.

Team members will be pasting one of these 10 cities/counties into the Area column and selecting either “Experimental” or “Written” in the LOB column.

I want their choices to automatically populate words into the District Column. Some have a 1 to 1 relationship, others have a 1 to many relationship, and all of the Areas that will automate to “Socal” in the District column need to have either “Experimental” or “Written” follow “Socal” in the District column based off the LOB selection. Basically, I’m hoping to write a column formula so that the District Column will populate this:

·        Seattle will always be Pacific North West

·        Denver will always be Rocky Mountain

·        Phoenix will always be Desert

·        Both Sacramento and Redding will always be Norcal

·        Los Angeles, Orange County, Inland Empire, Riverside, and Bakersfield will always be either “Socal Experimental” or “Socal Written”

Again, this MUST BE a column formula.

Ideally, the sheet would look like this. People would paste in the Area column and select from the two options in the LOB column, and the District column would auto-populate based off the written formula.

This is the formula I have started, but a 0 gets put in front of a lot of the Districts.

=IF([Area]@row

= "Los Angeles",

"SOCAL") + IF([Area]@row = "Orange

County", "SOCAL") + IF([Area]@row = "Inland

Empire", "SOCAL") + IF([Area]@row = "Riverside",

"SOCAL") + IF([Area]@row = "Bakersfield",

"SOCAL") + IF([Area]@row = "Sacramento",

"Norcal") + IF([Area]@row = "Redding",

"Norcal") + IF([Area]@row = "Phoenix",

"Desert") + IF([Area]@row = "Denver",

"Rocky Mountain") + IF([Area]@row = "Seattle",

"Pacific North West")

And I don't even know where to start to add LOB in. Please help!

Thanks in advance!!

bisaacs

Hey @aharritt,

So the problem you're having with your current formula is using the "+" sign, as that just combines values, rather than being a logical operator. You're on the right track with using chained IF statements (along with AND/OR functions for multiple potential inputs, but you'd want to actually format it to look something like this:

=IF(AND(OR(Area@row = "Los Angeles", Area@row = "Orange County"), LOB@row = "Experimental"), "SOCAL Experimental", IF(OR(Area@row = "Sacramento", Area@row = "Redding"), "NORCAL"))

Working inward out, the logic expression first checks to see if Area is either LA or OC, then it checks if LOB states "Experimental". If it all lines up, then it returns "SOCAL Experimental", if it's false, then it moves on to the next logic expression (this time looking for NORCAL locations on the Area cell). You should be able to expand this to include all the other logic expressions for the rest of your sheet!

Hopefully this makes sense, and let me know if you have any questions!

bisaacs

Hey @aharritt,

So the problem you're having with your current formula is using the "+" sign, as that just combines values, rather than being a logical operator. You're on the right track with using chained IF statements (along with AND/OR functions for multiple potential inputs, but you'd want to actually format it to look something like this:

=IF(AND(OR(Area@row = "Los Angeles", Area@row = "Orange County"), LOB@row = "Experimental"), "SOCAL Experimental", IF(OR(Area@row = "Sacramento", Area@row = "Redding"), "NORCAL"))

Working inward out, the logic expression first checks to see if Area is either LA or OC, then it checks if LOB states "Experimental". If it all lines up, then it returns "SOCAL Experimental", if it's false, then it moves on to the next logic expression (this time looking for NORCAL locations on the Area cell). You should be able to expand this to include all the other logic expressions for the rest of your sheet!

Hopefully this makes sense, and let me know if you have any questions!