Conditional Formats using Conditional Fomula or another method

[MsCrystal1]

I'm stumped I have multiple rows and columns that are independent of each other. I need to setup a legend that's automated with three colors.

The spreadsheet is setup like this ... [TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD]L[/TD]
[TD]H[/TD]
[TD]W[/TD]
[TD]SD[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Goal[/TD]
[TD].2[/TD]
[TD].3[/TD]
[TD].3[/TD]
[TD].4[/TD]
[TD].6[/TD]
[TD].6[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Actual[/TD]
[TD].1[/TD]
[TD].4[/TD]
[TD].3[/TD]
[TD].5[/TD]
[TD].7[/TD]
[TD].6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Goal[/TD]
[TD].4[/TD]
[TD].5[/TD]
[TD].7[/TD]
[TD].4[/TD]
[TD].4[/TD]
[TD]0.[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Actual[/TD]
[TD].5[/TD]
[TD].5[/TD]
[TD].3[/TD]
[TD].10[/TD]
[TD].5[/TD]
[TD].10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now the legend would need to function so that Goal/Actual for set the following colors
[TABLE="width: 536"]
<tbody>[TR]
[TD] Green[/TD]
[TD]Meeting 90% or more of 2014 goals[/TD]
[/TR]
[TR]
[TD] Yellow[/TD]
[TD]Achieve 60% to 89% of 2014 goals[/TD]
[/TR]
[TR]
[TD] Red[/TD]
[TD]Achieve less than 60% of 2014 goals[/TD]
[/TR]
[TR]
[TD] Gray[/TD]
[TD]No goal[/TD]
[/TR]
</tbody>[/TABLE]
So C2/C1 has to be evaluated against the above criteria
.2/.1=.5 which means it should be red I need the color to apply to C1 and C2
and then the same for D2/D1, E2/E1, F2/F1 and G2/G1 then move down to D5/D4, E5/E4

I hope this makes sense ... I have no clue where to start although it seems like this should be fairly easy to figure out.

Any assistance is appreciated.

 

[DRSteele]

I think I can help you. Conditional Formatting can be a royal pain, so put on your seatbelt. In my example below, select cell B2.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =B2=0 |Format, Fill, select a grey|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =AND(B3/B2>0,B3/B2<0.6) |Format, Fill, select a red|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =AND(B3/B2>=0.6,B3/B2<0.9) |Format, Fill, select a yellow|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =B3/B2>=0.9 |Format, Fill, select a green|OK.

Then go to cell B3 and repeat verbatim the above four steps for CondFrmt.

Select cells B2 and B3, right-click copy, select cells C2 to G3 and right-click Paste Formatting. Select cells B6 to G7 and right-click Paste Formatting. Select other cells in any subsequent rows that are structured identically to our A1 to G3 and right-click Paste Formatting.

[Notice I included in A9 to A12 text of the formatting's logical tests so you can copy and paste them instead of typing them all, should you choose to do so.]

Excel 2012
	A	B	C	D	E	F	G
1		S	L	H	W	SD	V
2	Goal	0.2	0.3	0.3	0.4	0.6	0.6
3	Actual	0.1	0.4	0.3	0.5	0.7	0.6
4	Total
5
6	Goal	0.4	0.5	0.7	0.4	0.4	0
7	Actual	0.5	0.5	0.3	0.1	0.5	0.1
8
9	=B2=0
10	=AND(B3/B2>0,B3/B2<0.6)
11	=AND(B3/B2>=0.6,B3/B2<0.9)
12	=B3/B2>=0.9
Sheet56

 

[MsCrystal1]

Thanks for this I have to play with the relative references because it's not picking up the correct field when I copy it over.

 

[MsCrystal1]

I think I can help you. Conditional Formatting can be a royal pain, so put on your seatbelt. In my example below, select cell B2.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =B2=0 |Format, Fill, select a grey|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =AND(B3/B2>0,B3/B2<0.6) |Format, Fill, select a red|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =AND(B3/B2>=0.6,B3/B2<0.9) |Format, Fill, select a yellow|OK.

In the ribbon, select Conditional Formatting|New Rule|Use formula...|type this into the Format values box =B3/B2>=0.9 |Format, Fill, select a green|OK.

Then go to cell B3 and repeat verbatim the above four steps for CondFrmt.

Select cells B2 and B3, right-click copy, select cells C2 to G3 and right-click Paste Formatting. Select cells B6 to G7 and right-click Paste Formatting. Select other cells in any subsequent rows that are structured identically to our A1 to G3 and right-click Paste Formatting.





[Notice I included in A9 to A12 text of the formatting's logical tests so you can copy and paste them instead of typing them all, should you choose to do so.]

Excel 2012

	A	B	C	D	E	F	G
S	L	H	W	SD	V
Goal
Actual
Total
Goal
Actual
=B2=0
=AND(B3/B2>0,B3/B2<0.6)
=AND(B3/B2>=0.6,B3/B2<0.9)
=B3/B2>=0.9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.2[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0.6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.1[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]0.6[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0.4[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

b3/b2

Sheet56​

I'm not able to copy the formula to the other cells it doesn't copy correctly.

Essentially I need b3/b2,c3/c2,d3/d2,e3/e2 then change the color of both b3 and b2. B7/b6,c7/c6,d7/d6,e7/e6 change the color for row 6 and 7 and repeat for each grouping

 

[DRSteele]

I don't see any problem with the above procedure. Perhaps if you try it again in a blank workbook we can get to the bottom of it.

 

[MsCrystal1]

It's not changing row 3. I know I need to apply the conditional formatting to both row but then when I copy it changes the references.

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: left"]Goal[/TD]
[TD="class: xl64, width: 64"]0.2[/TD]
[TD="class: xl64, width: 64"]0.3[/TD]
[TD="class: xl64, width: 64"]0.3[/TD]
[TD="class: xl64, width: 64"]0.4[/TD]
[TD="class: xl64, width: 64"]0.6[/TD]
[TD="class: xl64, width: 64"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: left"]Actual[/TD]
[TD="class: xl64, width: 64"]0.1[/TD]
[TD="class: xl64, width: 64"]0.4[/TD]
[TD="class: xl64, width: 64"]0.3[/TD]
[TD="class: xl64, width: 64"]0.5[/TD]
[TD="class: xl64, width: 64"]0.7[/TD]
[TD="class: xl64, width: 64"]0.6[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: left"]Total[/TD]
[TD="class: xl64, width: 64"] RED[/TD]
[TD="class: xl64, width: 64"]Green [/TD]
[TD="class: xl64, width: 64"] Green[/TD]
[TD="class: xl64, width: 64"] Green[/TD]
[TD="class: xl64, width: 64"]Green [/TD]
[TD="class: xl66, width: 64"]Green [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: left"]Goal[/TD]
[TD="class: xl64, width: 64"]0.4[/TD]
[TD="class: xl64, width: 64"]0.5[/TD]
[TD="class: xl64, width: 64"]0.7[/TD]
[TD="class: xl64, width: 64"]0.4[/TD]
[TD="class: xl64, width: 64"]0.4[/TD]
[TD="class: xl64, width: 64"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: left"]Actual[/TD]
[TD="class: xl64, width: 64"]0.5[/TD]
[TD="class: xl64, width: 64"]0.5[/TD]
[TD="class: xl64, width: 64"]0.3[/TD]
[TD="class: xl64, width: 64"]0.1[/TD]
[TD="class: xl64, width: 64"]0.5[/TD]
[TD="class: xl64, width: 64"]0.1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] Green [/TD]
[TD="class: xl64, width: 64"] Green[/TD]
[TD="class: xl64, width: 64"] Red [/TD]
[TD="class: xl64, width: 64"] Red [/TD]
[TD="class: xl64, width: 64"] Green [/TD]
[TD="class: xl66, width: 64"] Gray [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
</tbody>[/TABLE]
In my actual table it's dividing by the total. Which is below the actual. I need to know how to make sure it continues to pick up the result of B2/B3 for both B2 and B3 and then C3/C2 for C2 and C3 so on and so forth

 

[MsCrystal1]

Okay I fixed that issue ... my issue is now trying to figure out how to incorporate a rule for when the denominator is zero and the result yields an error. Presently it just remains white but I need it to be red so ... for example is if B3=0 as opposed to .1 which would yield an #div/0

 

[DRSteele]

You're all over the place here! Row 2 is the denominator and when it is 0 the colour is grey; changing B3 to 0 would not create an error since O is the numerator.