Multiple IF AND formula for two columns

Platform discussions Special workflows & use cases
1

I’m working on a prioritization matrix and I have three columns - Impact, Effort, and Priority. I would like to set a formula to auto-populate the Priority column based upon the numbers keyed into the Impact and Effort columns.

If Impact is >5 and Effort is >5, the Priority = “Major Project”
If Impact is >5 and Effort is <=5, the Priority = “Quick Win”
If Impact is <=5 and Effort is <=5, Priority = “Fill-Ins”
If Impact is <=5 and Effort is >5, Priority = “Thankless Tasks”

To complicate things a little further, I do have an automation on this board that adds an item to this board from other boards if certain criteria is met, and auto-assigns the Priority as two other labels I have. What formula can I use to accomplish the above and can this all be done in the same Priority column?

2

@jhmiletwo

Here is one way to do this:

  1. Add an additional column “Priority Override”.
  2. Change your automation to set the “Priority Override” column.
  3. Use this formula in the Priority column:
IF({Priority Override}, {Priority Override},
IF(AND({Impact} > 5, {Effort} > 5), "Major Project", "") &
IF(AND({Impact} > 5, {Effort} <= 5), "Quick Win", "") &
IF(AND({Impact} <= 5, {Effort} <= 5), "Fill-Ins", "") &
IF(AND({Impact} <= 5, {Effort} > 5), "Thankless Tasks", ""))

Jim - Subscribe to The Monday Man
Watch Our Latest Video: Quick Tip - The Trick to Editing Custom Automations