How to show most frequent status per unique groupings?

yh374

Hi,

So I have the following sheet set up for projects where each Group may have more than one project in different statuses:

The order of status are the following: 1) Started, 2) In Progress and 3) Completed. I am trying to set up a formula that will return the most frequently populated Status per unique Group and also looks to return the highest ranked status if it's stuck between two Status values. Something like this:

So for example, since Group 1 has more projects with "In Progress Status", I want "In Progress" to show up as Overall Status. And since Group 2 has only two projects in different status, I want to return the highest ranked status between the two, which is "Completed."

Is there a possible formula or workaround to do this?

Thank you for any ideas and help!

vh.

Hi @yh374 It appears your question might have already been addressed here:

Conditional Formatting - One rule on multiple columns

Wouter Hijzen

Jul 24, 2018

Hello,

To make our Smartsheets more legible when used for input requests, we use conditional formatting. 

For example, if we have a table used for percentage-splits with 12 columns and 100 rows we generally pre-populate these inputs where we can with formulas. If the formulas show 0%, it becomes messy fast. To clean it up, we use conditional formatting to say "if value is 0, then white text" (on white background this becomes invisible). Currently, setting this up is extremely time consuming when building new models as you have to set up a rule for conditional formatting for each column and select to which column to apply it. 

The standard option to apply formatting to entire row is there - is it possible to add as a standard "to entire column"? Also, it is possible to apply a conditional formatting to multiple columns but not to apply one rule to multiple columns. Being able to apply one rule to multiple columns and set application to "to entire column" would make conditional formatting much easier/more versatile.

Thanks!

Wouter

---

View Post

Additionally, this may be helpful:

Manage conditional formatting rules | Smartsheet Learning Center

https://help.smartsheet.com/articles/2482629-manage-conditional-formatting-rules

You can edit your conditional formatting rules, clone them, turn them on or off, or delete them entirely.

-

✅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!

yh374

Hi vh.,

That did not answer my question; I am not looking for a conditional formatting rule. I am looking to be able to return a frequent value (a mode) for each unique group. Thanks!