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.