Formula for Priority Cell

AB

Hello All:

 

I am having a difficult time creating a formula that will give me Priority. Below are my rules for Priority Status. Thanks

 

Urgency High

If Impact High and Urgency High, then Priority is 1

If Impact Med and Urgency High, then Priority 2

If Impact Low and Urgency High, then Priority 3

 

Urgency Med

If Impact High and Urgency is Med, then Priority 2

If Impact Med and Urgency is Med, then Priority 3

If Impact Low and Urgency is Med, then Priority 4

 

Urgency Low

If Impact High and Urgency is Low, then Priority 3

If Impact Med and Urgency is Low, then Priority 4

If Impact Low and Urgency is Low, then Priority 5

AB

Here is the first part of my formula for Urgency High and Impact High:

=IF(AND(Impact12 = "High", Urgency12, "High"), "1"))

Andrée Starå

Hi,

Try something like this.

=IF(AND(Impact@row = "High"; Urgency@row = "High"); 1; IF(AND(Impact@row = "Med"; Urgency@row = "High"); 2; IF(AND(Impact@row = "Low"; Urgency@row = "High"); 3; IF(AND(Impact@row = "High"; Urgency@row = "Med"); 2; IF(AND(Impact@row = "Med"; Urgency@row = "Med"); 3; IF(AND(Impact@row = "Low"; Urgency@row = "Med"); 4; IF(AND(Impact@row = "High"; Urgency@row = "Low"); 3; IF(AND(Impact@row = "Med"; Urgency@row = "Low"); 4; IF(AND(Impact@row = "Low"; Urgency@row = "Low"); 5)))))))))

The same version but with the below changes for your and others convenience.

=IF(AND(Impact@row = "High", Urgency@row = "High"), 1, IF(AND(Impact@row = "Med", Urgency@row = "High"), 2, IF(AND(Impact@row = "Low", Urgency@row = "High"), 3, IF(AND(Impact@row = "High", Urgency@row = "Med"), 2, IF(AND(Impact@row = "Med", Urgency@row = "Med"), 3, IF(AND(Impact@row = "Low", Urgency@row = "Med"), 4, IF(AND(Impact@row = "High", Urgency@row = "Low"), 3, IF(AND(Impact@row = "Med", Urgency@row = "Low"), 4, IF(AND(Impact@row = "Low", Urgency@row = "Low"), 5)))))))))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

Paul Newcome

Depending on your setup, you could also use a "Priority Table" of sorts. Take a look at the screenshot below. We are basically using an INDEX function to look at the table, using a MATCH function to determine the row number off of the Impact, and another MATCH function to determine the column number off of the Urgency.

 

The formula used in the screenshot is as follows:

 

=INDEX($[High Urgency]$2:$[Low Urgency]$4, MATCH($Impact@row, $ImpactTable$2:$ImpactTable$4, 0), MATCH($Urgency@row, $[High Urgency]$1:$[Low Urgency]$1, 0))

.

All you would need to do is update column names and row references based on your actual column names and the actual row numbers used.

AB

Excellent! It worked perfectly. Thanks Andree

Andrée Starå

Happy to help!

AB

I am using this same formula approach for a different cell but receiving an argument error. Not sure what I am doing wrong. Can you Help?

 

=COUNTIFS(AND(Priority@row = "P1", Status@row = "Open"))

Andrée Starå

Happy to help!

You don't need the AND function in the formula.

Did it work?

AB

No, incorrect argument

 

=COUNTIF(Priority@row, ="P1", Status@row = "Open")

Andrée Starå

Change to COUNTIFS.

Paul Newcome

and add a comma after Status@row.

AB

Ok, that worked but for some reason my results are 0. However when I look at the Priority and Status Column, I have several P1 that meet the criteria.

Does Sub Task impact the results?

 

 

Paul Newcome

You are using specific cell references in your formula that are pointing to the cells on the same rows as the formula. 

 

Looking across the same row, you will never have a count higher than 1.

 

To look at the entire columns for your ranges, you will need to specify that in your formula.

 

[Column Name]:[Column Name]

 

Repeating the column name with a separator of a colon and no row references with tell the formula to look at the entire column.

Christine S

Thanks everyone who contributed to this thread... Below is what I'm using the populate priority.

Sharing because my formula was coming up as #UNPARSEABLE and I couldn't figure out why.

Turns out that I needed to also include quotes around my return values since they also contained text, and not just numbers likes the examples above.

Hope this helps someone :)

Good Luck!

=IF(AND(Impact@row = "0 - Critical", Urgency@row = "0 - Emergency"), "0 - Emergency", 

IF(AND(Impact@row = "0 - Critical", Urgency@row = "1 - Critical"), "1 - High", 

IF(AND(Impact@row = "0 - Critical", Urgency@row = "2 - Significant"), "1 - High", 

IF(AND(Impact@row = "0 - Critical", Urgency@row = "3 - Minor"), "2 - Medium", 

IF(AND(Impact@row = "1 - Major", Urgency@row = "0 - Emergency"), "1 - High", 

IF(AND(Impact@row = "1 - Major", Urgency@row = "1 - Critical"), "1 - High", 

IF(AND(Impact@row = "1 - Major", Urgency@row = "2 - Significant"), "2 - Medium", 

IF(AND(Impact@row = "1 - Major", Urgency@row = "3 - Minor"), "3 - Low", 

IF(AND(Impact@row = "2 - Significant", Urgency@row = "0 - Emergency"), "1 - High", 

IF(AND(Impact@row = "2 - Significant", Urgency@row = "1 - Critical"), "2 - Medium", 

IF(AND(Impact@row = "2 - Significant", Urgency@row = "2 - Significant"), "3 - Low", 

IF(AND(Impact@row = "2 - Significant", Urgency@row = "3 - Minor"), "3 - Low",

IF(AND(Impact@row = "3 - Minor", Urgency@row = "0 - Emergency"), "2 - Medium", 

IF(AND(Impact@row = "3 - Minor", Urgency@row = "1 - Critical"), "3 - Low", 

IF(AND(Impact@row = "3 - Minor", Urgency@row = "2 - Significant"), "3 - Low", 

IF(AND(Impact@row = "3 - Minor", Urgency@row = "Low"), "3 - Low")))))))))))))))