Syntax Error

keesuri25

=IF(AND([Engagement Phase]:[Engagement Phase]="COMPLETED", [Production Status]:[Production Status]="Production Ineligible") 0,1)

I am sure its something minor.. But just not able to fix this.

Genevieve P.

Hi @keesuri25

Can I clarify exactly when you want to see 0 and when you want 1?

If I'm understanding you correctly, as soon as the Engagement Phase is "Completed" you want it to show 1, EXCEPT if the Production Status is "ineligible".

If that's correct, we can swap around the 1 and 0 at the end of the formula and adjust it to say <> or NOT equal to "ineligible".

Try this:

=IF(AND([Engagement Phase]@row = "COMPLETED", [Production Status]@row <> "Production Ineligible"), 1, 0)

Should this still not work for you, it would be helpful to know every possible selection in each column and all of the combinations that end up in either 1 or 0.

Cheers!

Genevieve

Andrée Starå

Hi @keesuri25

I hope you're well and safe!

What do you want the formula to do?

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

keesuri25

Engagement status - column

Production status - column

conversion eligible- column

So if Engagement status is completed and then for that row Production status is set to Production Ineligible then put 0 in conversion eligible column, if not 1.

Andrée Starå

@keesuri25

Do you want to change the value on each row in the conversion eligible column?

keesuri25

yes. each row on conversion eligible column should change to 1 or 0 depending on the statuses set.

Andrée Starå

@keesuri25

Try something like this and convert it to a column formula if it works as expected.

=IF(AND([Engagement Phase]@row = "COMPLETED", [Production Status]@row = "Production Ineligible"), 0, 1)

Did it work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

keesuri25

Thank you so much. That worked. Have a great weekend :D

keesuri25

The formula is not working completely. I think i need to add an additional clause/check.

The problem is that if the Engagement Phase is any other status meaning IN Progress or on hold, then the above formula is counting it as Conversion Eligible. As it puts a 1 in conversion eligible column.

I need to be like only for completed engagements that have a product in eligible then conversion eligible is 0. So any other engagement phase other than completed - conversion eligible is still 0.

Any thoughts ?

Genevieve P.

Hi @keesuri25

Can I clarify exactly when you want to see 0 and when you want 1?

If I'm understanding you correctly, as soon as the Engagement Phase is "Completed" you want it to show 1, EXCEPT if the Production Status is "ineligible".

If that's correct, we can swap around the 1 and 0 at the end of the formula and adjust it to say <> or NOT equal to "ineligible".

Try this:

=IF(AND([Engagement Phase]@row = "COMPLETED", [Production Status]@row <> "Production Ineligible"), 1, 0)

Should this still not work for you, it would be helpful to know every possible selection in each column and all of the combinations that end up in either 1 or 0.

Cheers!

Genevieve

keesuri25

This worked perfectly. Thank you so much. And sorry about not explaining the context correctly.

Really appreciate your prompt responses and guidance.