Excel Formula to create a traffic light system

[suemon]

I am trying to evaluate our sales information system and put together a simple way to evaluate how my salespeople are performing in their task of updating and keeping this info clean.
I have established measures on "how many" of the different records are "correct" but I need to put these into a basic excel layout showing a "traffic light" system. ie. I can show how many records are correct vs incorrect and what the percentages are but I need be able to Evaluate this with conditional formatting withing this spreadsheet. Simple enough? Sure!
My question is... I am a bit of a hard bugger and consider <5% incorrect a green light
5 - 20 % amber
20%+ RED!!!

How do I apply multiple arguments to one cell.... or can I?
I want to be able to say that...
If anything is "red" then "red" BUT
if this is not the case then "take the average of all measures" and then apply conditional formatting to give the appropriate color. (either amber or green)
How many more blinking columns will I need to add?
Make sense?

Hoping someone can help....
Suemon

 

[SkipVought]

Hi,

Format/Conditional Formatting...

you can set up to 3 criteria for 3 different formats.

Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]

 

[suemon]

I understand that and have already done it. Maybe I'm just not seeing the forest for the trees. Here is what I am trying to do.
Columns 1 through 3 = percentage of correct record types A,B,and C
conditionally formatted to turn red, amber or green dependant on value
Column 4 needs to be conditionally formatted such that if any of the first three columns are 'red' then column 4 turns red....... else it needs to show an average of the first three columns and be conditionally formatted to turn red, amber or green dependant on value. This column is meant to represent an overall rating.

Does this make any sense?

 

[xlbo]

yup - what exactly are you having problems with? seems simple enough.

 

[suemon]

You will have to forgive me but this is the first time I have tried this and maybe I am not explaining correctly... let me try again. Also I am trying to keep the example simple bear with me...
I have no trouble conditionally formatting the first three columns. They have three conditions each.
i.e.
if below 5% fill background with green
if tween 5 and 20% fill background with amber
if over 20% fill background with red.

The problem comes with the 4th column that is an overall rating.
Simple enough to get an average value and turn the background amber or green but how to I stipulate that if any one of the first three columns ends up red, then make this cell in column 4 red (but also show me the average number.
example
Column 1 shows 6% (therefore gets an amber background)
Column 2 shows 2% (therefore gets a green background)
Column 3 shows 21% (therefore gets a red background)
If I used the same formatting in column 4 for the overall (which would be an average = 10%ish) then the background would be amber.
What I need the fourth col to do is average the percentages in the first three columns, turn them red amber or green BUT if ANY of the 3 columns shows a red then make the 4th col red.
Kinda like overriding the conditional formatting with another condition.

 

[xlbo]

maybe

=OR(Col1>5%,Col2>5%,Col3>5%)

in the formula part of conditional formatting on the last column - this should turn the 4th cell red if any of the others are red

 

[suemon]

The solution you give seems to turn the cells red with the word 'true' appearing regardless of anything else.
example
col 1 = 2% (green)
col 2 = 6% (amber)
col 3 = 21% (red)
col 4 = =+(Col1+Col2+Col3)/3 result is 10 which turns it amber.

How do I apply this type of formula to the 4th col in addition to the conditional formatting I've got the 4th columns cell value (which is the average of first 3 cols)?

 

[xlbo]

the formula I gave is to be used within the conditional formatting - NOT as a formula on the spreadsheet

 

[suemon]

Got that but it is still not colouring the column correctly. Don't know what I'm doing wrong.
Am using the Formula Is and putting your formula into the third condition (which colours cell red)
Is this wrong.?

 

[SkipVought]

Are you using NAMED RANGES, like Col1, Col2...???

If not then you must use a CELL REFRERENCE for each (absolute column, relative row).

So if your 3 columns are A, B & C and the first row in your selection is row 2...
[tt]
=OR($A2>5%,$B2>5%,$C2>5%)
[/tt]


Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]

 

[xlbo]

no, no, no

that formula is seperate - you cannot use the formulae you currently have for your 4th cell. If you could, we would not be here answering this question. This is a forum where people learn - not where we do your job for you. I made a suggestion to look at an OR formula - can you not interpret from there to see what you would need to do ???

Your first CF formula is:
=OR($A2>0.05,$B2>0.05,$C2>0.05,average($A2:$C2>0.05)

This checks for individual elements being over 5% OR the average being over 5%

The other 2 formulae would be the same as you already have for Amber & Green but taking the AVERAGE of the 3 cells to do so...

 

[suemon]

Wasn't asking for you to do this for me!!! You are right... this is a place where we are supposed to learn, it's just that some of us are not quite as advanced as you are. Absolutely no need for the tone but thanks for your time.

 

[combo]

Xlbo' solution works (with formula is and Or(...)), but mind the details. Excel uses the first format that satisfies conditions. So in condition 1 start with the most restrictive, in your case 20%, in condition 2 compare cells to 5%.

combo

 

[SkipVought]

Travail is often the mother of learning.

Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]