Formula for using priority symbols

alexandra.richardson

Hi! I want to create a formula where I can count the children under each header in my Task Name column I have in my project plan. Each child in column Task Name has a high, medium, and low priority symbol (the down blue arrow, yellow dash, and red exclamation point) in a column called Priority. I want the header to calculate if the priority of that grouping is mostly low, medium, or high. Can I get some formula help?

KPH

Hi @alexandra.richardson

I've tried to think of ways to do this using COLLECT, INDEX, MAX, LARGE, and cannot see a method that doesn't require an additional column. But seeing as there are only 3 things to count, I thought we could do a COUNTIF for each symbol and compare the answers using IF AND.

This is my thought:

=IF(AND(COUNTIF(CHILDREN(Priority@row), "low") > COUNTIF(CHILDREN(Priority@row), "medium"),

COUNTIF(CHILDREN(Priority@row), "low") > COUNTIF(CHILDREN(Priority@row), "high")), "Low",

IF(AND(COUNTIF(CHILDREN(Priority@row), "high") > COUNTIF(CHILDREN(Priority@row), "medium"),

COUNTIF(CHILDREN(Priority@row), "high") > COUNTIF(CHILDREN(Priority@row), "low")), "High",

IF(AND(COUNTIF(CHILDREN(Priority@row), "medium") > COUNTIF(CHILDREN(Priority@row), "low"),

COUNTIF(CHILDREN(Priority@row), "medium") > COUNTIF(CHILDREN(Priority@row), "high")), "Medium",

"tie")))

Essentially... if the count of the low-priority symbols in the child rows is greater than the count of medium-priority symbols and high-priority symbols then return Low.

If not, if the count of the high-priority symbols in the child rows is greater than the count of medium-priority symbols and low-priority symbols then return High.

If not, if the count of the medium-priority symbols in the child rows is greater than the count of low-priority symbols and high-priority symbols then return Medium.

If none of those things is true then two counts must be equal. Therefore return the word "tie".

The output looks like this. The formula is on the gray rows in the Priority column.

Does this help? I hope so!

Humashankar

Hi Alex - Interesting topic to clarify:

Try using formulas to assign priority symbols based on certain criteria. One common way to do this is by using conditional statements combined with symbols or text to represent priorities. Here's an example of how you can achieve this:

Let's say you have a column named "Priority" where you want to display priority symbols based on the values in another column called "Priority Score". You can use the following formula in the "Priority" column:

In this formula:

If the "Priority Score" is greater than or equal to 80, it will display "High".

If the "Priority Score" is between 60 and 79, it will display "Medium".

If the "Priority Score" is between 40 and 59, it will display "Low".

If the "Priority Score" is less than 40, it will display "None".

You can adjust the thresholds and priority symbols according to your specific requirements. Additionally, you can use symbols like asterisks (*) or exclamation marks (!) instead of text if you prefer. 

Just an use case, you could replace "High" with "!!!" to represent high priority.

Thanks

Keep us posted if this helps !!

alexandra.richardson

These are both ridiculously helpful, thank you!!!

KPH

Glad we could help.

aschwab

what am I unable to see… what is the ridiculously helpful formula?
I am trying to make it so that
41 and Up is High, 34-40 is Medium and 33 and lower is Low in a Total Score Column, will generate these priority results in the Priority Level column next to it set for icons.

KPH

Hi

The formula is one that sets the parent priority based on the "average" of the child priorities. This:

=IF(AND(COUNTIF(CHILDREN(Priority@row), "low") > COUNTIF(CHILDREN(Priority@row), "medium"), COUNTIF(CHILDREN(Priority@row), "low") > COUNTIF(CHILDREN(Priority@row), "high")), "Low",IF(AND(COUNTIF(CHILDREN(Priority@row), "high") > COUNTIF(CHILDREN(Priority@row), "medium"),COUNTIF(CHILDREN(Priority@row), "high") > COUNTIF(CHILDREN(Priority@row), "low")), "High",IF(AND(COUNTIF(CHILDREN(Priority@row), "medium") > COUNTIF(CHILDREN(Priority@row), "low"),COUNTIF(CHILDREN(Priority@row), "medium") > COUNTIF(CHILDREN(Priority@row), "high")), "Medium","tie")))

But that is not the question you have. It sounds like you just need an IF to convert a numeric value into High, Medium, and Low.

If you have your score in a Total Score column like this:

Then you can use a simple nested IF to define the priority level like this:

The formula is:

=IF([Total Score]@row >= 41, "High", IF([Total Score]@row <= 33, "Low", "Medium"))

This says if the Total Score is greater than or equal to 41, return "High". If that is not true, if the Total Score is less than or equal to 33, return "Low". If that is not true, return "Medium".

To convert the text to a symbol the Priority Level column must be set up as a symbol column, like this:

Hope that is what yo need!