If Statement to show task status

Karen_Piotrowsk · ‎Aug 03, 2020

I would like to create an if statement and could use some assistance …

If cell A is not checked, outcome “planned”
If cell A is checked, outcome "in progress
If cell B is checked, outcome “complete”

I hope this makes sense. Thank you for any help you can offer to this newbie :slightly_smiling_face:

Justin_Barrett · ‎Aug 03, 2020

In this case, each test is a single IF() function, with the three functions nested inside each other. If the first one matches, output the desired info; otherwise pass it to the next level deeper, and so on.

IF(NOT({cell A}), "planned", IF({cell A}, "in progress", IF({cell B}, "complete")))

Here’s the same thing broken out so you can see the nesting levels more easily:

IF(
	NOT({cell A}),
	"planned",
	IF(
		{cell A},
		"in progress",
		IF(
			{cell B},
			"complete"
		)
	)
)

Karen_Piotrowsk · ‎Aug 04, 2020

Thank you Justin! I didn’t get any errors using this formula but the outcome is not working like I thought it would. The complete outcome does not seem to want to over ide the “in progress” outcome. Does that make any sense?

Justin_Barrett · ‎Aug 04, 2020

Not exactly. Could you share a screenshot showing the results, and explaining what you want it to do instead of the current behavior?

Karen_Piotrowsk · ‎Aug 04, 2020

Thanks again for all your help! What I would like to happen is…When DONE1 is checked the outcome in Calculated Progress would be “In progress”. When nothing is checked, it is “planned”. Once DONE4 is checked the outcome would be “complete”

Justin_Barrett · ‎Aug 04, 2020

How do {Done2} and {Done3} factor in? Do they have any influence on the progress level? I’ve got an idea on how to fix and simplify the formula, but I want to know how those two fields contribute before starting to work on it.

Karen_Piotrowsk · ‎Aug 04, 2020

Well what you can’t see are the fields I hid so that the critical parts would show on the screen for you. These Done columns relate to promotion dates. Once they are promoted then our social media manager is supposed to check them off. I would entertain eliminating the checkbox columns altogether and just have thing status be calculated based on the date entered of when the promotion was done.

Justin_Barrett · ‎Aug 04, 2020

Gotcha. That may be possible, but let’s tackle the current situation for now. Here’s a revised formula that should work:

IF(Done1, IF(Done4, "Complete", "In Progress"), "Planned")

Karen_Piotrowsk · ‎Aug 04, 2020

Perfect, worked like a charm! I’d like to get better at understanding and writing my own formulas. I have read over some of the stuff on the airtable support page. But, reading about it isn’t that helpful to me. I really need more hands on practice. Can you recommend and good resources? I really appreciate all your help, Justin!

Justin_Barrett · ‎Aug 04, 2020

For me, it’s all about the first part of that statement: hands-on practice. I’m the type that just likes to play with stuff. When I first started with Airtable and found this community, I did a lot more observing than contributing, but I would still experiment on the side when someone presented a problem, which gave me a chance to try ideas and learn what does and doesn’t work. Interesting challenges are always coming along, so my best suggestion is to peruse the posts, try your hand at solving a variety of problems, and learn from others’ solutions as well.

On a related note, I’ve got a (very-)slowly-growing YouTube channel where I’m trying to break down various problems I’ve run into, especially formulas. Maybe that will help as well. :slightly_smiling_face: