Conditional Formatting based on mode value in column

[CallumJ999]

Hello everyone,

As above I'm trying to apply conditional formatting which changes based on the mode value of a column in a table.
For example, in my column, I have various options, the 3 main being "In progress", "Build complete" and "Deployed"

I would like the Column header to be formatted so it's amber if the majority of the cells in the colum say "In progress", or be green if the majority say "Deployed"

Any ideas?
Thanks in advance!

 

[etaf]

you could use a count of those values using countif

what about build complete ?

so 2 rules in conditional formatting rules

using a formula

=COUNTIF(A2:A8,"in progress")>(COUNTIF(A2:A8,"deployed")

format amber

=COUNTIF(A2:A8,"deployed")>COUNTIF(A2:A8,"in progress")

format green

what happens if deployed = in progress

 

[CallumJ999]

Idealy I would like Red if the majority is in progress, amber if majority is build complete and green if majority is deployed.

If they are equal at any point then round down to the lower status, where in progress is lowest/first, then build complete, and deployed is highest/last status

 

[AliGW]

You will need to define "majority".

 

[CallumJ999]

You will need to define "majority".

Hmm, well whichever of the three occurs the most is the majority. However on second thought, one could occur more than the other two, but the other two combined might still be more meaning the first one isn't actually the majority. Hmm...

Maybe it would be better to have the target cell red if less than 50% of the cells in the column contain "Deployed", Amber if between 50-75%, Light Green from 75-99% and Dark Green for 100%. Could that be done?

 

[etaf]

that % is calculated on deployed only

so
count of deployed / total count of all < 50 % Red
count of deployed / total count of all < 75 % light green
count of deployed / total count of all = 100 % green - so 100% means they ARE ALL deployed

is that what you mean ?

 

[CallumJ999]

that % is calculated on deployed only

so
count of deployed / total count of all < 50 % Red
count of deployed / total count of all < 75 % light green
count of deployed / total count of all = 100 % green - so 100% means they ARE ALL deployed

is that what you mean ?

Yes, but would rather have amber for 50- 75%, light green between 75-99%, and green for 100% only. Oh and red for under 50%

 

[etaf]

ok
so assuming the column is G and starts at row 2 to 100 - adjust as required
=(COUNTIF($G$2:$G$100,"deployed")/counta($G$2:$G$100)
then in a conditional formatting
Make up rules and use a formula
put

=COUNTIF($G$2:$G$100,"deployed")/counta($G$2:$G$100)<0.5
Red

=COUNTIF($G$2:$G$100,"deployed")/counta($G$2:$G$100)<=0.75
format that rule as Amber

then
=COUNTIF($G$2:$G$100,"deployed")/counta($G$2:$G$100)<1
format that rule as light greeen

then the last rule
=COUNTIF($G$2:$G$100,"deployed")/counta($G$2:$G$100)=1
Green

But you need to put in this order and use STOP if TRUE

if you want to upload onto onedrive/dropbox a sample

 

[etaf]

heres an example
column G
G2 to G100

ignore the blanks - we can sort that out - if this is exactly what you want
in cell I7 is the actual %

have a play
https://www.dropbox.com/s/qa4rb55m0eicf8f/cond_format_etaf.xlsx?dl=0

 

[CallumJ999]

heres an example
column G
G2 to G100

ignore the blanks - we can sort that out - if this is exactly what you want
in cell I7 is the actual %

have a play
https://www.dropbox.com/s/qa4rb55m0eicf8f/cond_format_etaf.xlsx?dl=0

Thanks so much, that works perfectly. However, I overlooked something that's now become an obvious issue. I also have a cancelled option. Is there a way to keep all of the above formatting but to exclude cells that contain "Cancelled" from the percentage?