Conditional formatting based on text status of 3 cells
 

Hi

I have a spreadsheet where 1 cell is conditionally formatted based on the
text status of 3 other cells, the text options being good, bad or average.
The criteria is a follows:

If more than 2 goods the cell = green
if more than 2 bads the cell = red
If more that 2 averages or a mixture of all three the cell = amber

I would be very grateful if you could give me any help on the formula
required?

Conditional formatting based on text status of 3 cells
 

If you have 3 cells, then:

If more than 2 goods the cell = green
if more than 2 bads the cell = red

If more than 2 really means: if *all* 3 cells contain... Right?

Try this...

Assume you want to format D1 based on the contents of A1:C1.

Select cell D1
Goto the menu FormatConditional Formatting
Condition 1 - GREEN
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="good")
Click the Format button
Select the Patterns tab
Select a nice shade of GREEN
OK
Click the Add button
Condition 2 - RED
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="bad")
Click the Format button
Select the Patterns tab
Select a nice shade of RED
OK
Click the Add button
Condition 3 - AMBER
Select the Formula Is option
Enter this formula in the box on the right:
=COUNTA(A1:C1)=3
Click the Format button
Select the Patterns tab
Select a nice shade of AMBER (YELLOW ?)
OK out

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
Hi

I have a spreadsheet where 1 cell is conditionally formatted based on the
text status of 3 other cells, the text options being good, bad or average.
The criteria is a follows:

If more than 2 goods the cell = green
if more than 2 bads the cell = red
If more that 2 averages or a mixture of all three the cell = amber

I would be very grateful if you could give me any help on the formula
required?

Conditional formatting based on text status of 3 cells
 

I'm sorry I didn't explain myself properly

If 2 or more cells are good cell = green
if 2 or more cells are bad cell = red

the last option is if 2 or more cells are average or, if cells are 1 of each
i.e.
1 good, 1 bad and 1 average cell = amber

Regards
Sandy 82

"T. Valko" wrote:

If you have 3 cells, then:

If more than 2 goods the cell = green
if more than 2 bads the cell = red

If more than 2 really means: if *all* 3 cells contain... Right?

Try this...

Assume you want to format D1 based on the contents of A1:C1.

Select cell D1
Goto the menu FormatConditional Formatting
Condition 1 - GREEN
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="good")
Click the Format button
Select the Patterns tab
Select a nice shade of GREEN
OK
Click the Add button
Condition 2 - RED
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="bad")
Click the Format button
Select the Patterns tab
Select a nice shade of RED
OK
Click the Add button
Condition 3 - AMBER
Select the Formula Is option
Enter this formula in the box on the right:
=COUNTA(A1:C1)=3
Click the Format button
Select the Patterns tab
Select a nice shade of AMBER (YELLOW ?)
OK out

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
Hi

I have a spreadsheet where 1 cell is conditionally formatted based on the
text status of 3 other cells, the text options being good, bad or average.
The criteria is a follows:

If more than 2 goods the cell = green
if more than 2 bads the cell = red
If more that 2 averages or a mixture of all three the cell = amber

I would be very grateful if you could give me any help on the formula
required?

Conditional formatting based on text status of 3 cells
 

OK, use these formulas:

GREEN:

=COUNTIF(A1:C1,"good")=2

RED:

=COUNTIF(A1:C1,"bad")=2

AMBER:

Use the same formula *if all 3 cells* will contain any combination other
than those above.

=COUNTA(A1:C1)=3

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
I'm sorry I didn't explain myself properly

If 2 or more cells are good cell = green
if 2 or more cells are bad cell = red

the last option is if 2 or more cells are average or, if cells are 1 of
each
i.e.
1 good, 1 bad and 1 average cell = amber

Regards
Sandy 82

"T. Valko" wrote:

If you have 3 cells, then:

If more than 2 goods the cell = green
if more than 2 bads the cell = red

If more than 2 really means: if *all* 3 cells contain... Right?

Try this...

Assume you want to format D1 based on the contents of A1:C1.

Select cell D1
Goto the menu FormatConditional Formatting
Condition 1 - GREEN
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="good")
Click the Format button
Select the Patterns tab
Select a nice shade of GREEN
OK
Click the Add button
Condition 2 - RED
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="bad")
Click the Format button
Select the Patterns tab
Select a nice shade of RED
OK
Click the Add button
Condition 3 - AMBER
Select the Formula Is option
Enter this formula in the box on the right:
=COUNTA(A1:C1)=3
Click the Format button
Select the Patterns tab
Select a nice shade of AMBER (YELLOW ?)
OK out

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
Hi

I have a spreadsheet where 1 cell is conditionally formatted based on
the
text status of 3 other cells, the text options being good, bad or
average.
The criteria is a follows:

If more than 2 goods the cell = green
if more than 2 bads the cell = red
If more that 2 averages or a mixture of all three the cell = amber

I would be very grateful if you could give me any help on the formula
required?

Conditional formatting based on text status of 3 cells
 

Thats great, thank you so much for your help

"T. Valko" wrote:

OK, use these formulas:

GREEN:

=COUNTIF(A1:C1,"good")=2

RED:

=COUNTIF(A1:C1,"bad")=2

AMBER:

Use the same formula *if all 3 cells* will contain any combination other
than those above.

=COUNTA(A1:C1)=3

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
I'm sorry I didn't explain myself properly

If 2 or more cells are good cell = green
if 2 or more cells are bad cell = red

the last option is if 2 or more cells are average or, if cells are 1 of
each
i.e.
1 good, 1 bad and 1 average cell = amber

Regards
Sandy 82

"T. Valko" wrote:

If you have 3 cells, then:

If more than 2 goods the cell = green
if more than 2 bads the cell = red

If more than 2 really means: if *all* 3 cells contain... Right?

Try this...

Assume you want to format D1 based on the contents of A1:C1.

Select cell D1
Goto the menu FormatConditional Formatting
Condition 1 - GREEN
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="good")
Click the Format button
Select the Patterns tab
Select a nice shade of GREEN
OK
Click the Add button
Condition 2 - RED
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="bad")
Click the Format button
Select the Patterns tab
Select a nice shade of RED
OK
Click the Add button
Condition 3 - AMBER
Select the Formula Is option
Enter this formula in the box on the right:
=COUNTA(A1:C1)=3
Click the Format button
Select the Patterns tab
Select a nice shade of AMBER (YELLOW ?)
OK out

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
Hi

I have a spreadsheet where 1 cell is conditionally formatted based on
the
text status of 3 other cells, the text options being good, bad or
average.
The criteria is a follows:

If more than 2 goods the cell = green
if more than 2 bads the cell = red
If more that 2 averages or a mixture of all three the cell = amber

I would be very grateful if you could give me any help on the formula
required?

Conditional formatting based on text status of 3 cells
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
Thats great, thank you so much for your help

"T. Valko" wrote:

OK, use these formulas:

GREEN:

=COUNTIF(A1:C1,"good")=2

RED:

=COUNTIF(A1:C1,"bad")=2

AMBER:

Use the same formula *if all 3 cells* will contain any combination other
than those above.

=COUNTA(A1:C1)=3

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
I'm sorry I didn't explain myself properly

If 2 or more cells are good cell = green
if 2 or more cells are bad cell = red

the last option is if 2 or more cells are average or, if cells are 1 of
each
i.e.
1 good, 1 bad and 1 average cell = amber

Regards
Sandy 82

"T. Valko" wrote:

If you have 3 cells, then:

If more than 2 goods the cell = green
if more than 2 bads the cell = red

If more than 2 really means: if *all* 3 cells contain... Right?

Try this...

Assume you want to format D1 based on the contents of A1:C1.

Select cell D1
Goto the menu FormatConditional Formatting
Condition 1 - GREEN
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="good")
Click the Format button
Select the Patterns tab
Select a nice shade of GREEN
OK
Click the Add button
Condition 2 - RED
Select the Formula Is option
Enter this formula in the box on the right:
=AND(A1:C1="bad")
Click the Format button
Select the Patterns tab
Select a nice shade of RED
OK
Click the Add button
Condition 3 - AMBER
Select the Formula Is option
Enter this formula in the box on the right:
=COUNTA(A1:C1)=3
Click the Format button
Select the Patterns tab
Select a nice shade of AMBER (YELLOW ?)
OK out

--
Biff
Microsoft Excel MVP


"Sandy82" wrote in message
...
Hi

I have a spreadsheet where 1 cell is conditionally formatted based
on
the
text status of 3 other cells, the text options being good, bad or
average.
The criteria is a follows:

If more than 2 goods the cell = green
if more than 2 bads the cell = red
If more that 2 averages or a mixture of all three the cell = amber

I would be very grateful if you could give me any help on the
formula
required?