Conditional Formats using Conditional Fomula or another method

MsCrystal1

New Member
Joined
Jun 30, 2015
Messages
6
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 ...
ABCDEFGH
SLHWSDV
1Goal.2.3.3.4.6.6
2Actual.1.4.3.5.7.6
Total
4Goal.4.5.7.4.40.
5Actual.5.5.3.10.5.10
Total

<tbody>
</tbody>

Now the legend would need to function so that Goal/Actual for set the following colors
GreenMeeting 90% or more of 2014 goals
YellowAchieve 60% to 89% of 2014 goals
RedAchieve less than 60% of 2014 goals
GrayNo goal

<tbody>
</tbody>
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
ABCDEFG
1SLHWSDV
2Goal0.20.30.30.40.60.6
3Actual0.10.40.30.50.70.6
4Total
5
6Goal0.40.50.70.40.40
7Actual0.50.50.30.10.50.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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
ABCDEFG
1SLHWSDV
2Goal0.20.30.30.40.60.6
3Actual0.10.40.30.50.70.6
4Total
5
6Goal0.40.50.70.40.40
7Actual0.50.50.30.10.50.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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
 
Upvote 0
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.
 
Upvote 0
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.

Goal0.20.30.30.40.60.6
Actual0.10.40.30.50.70.6
Total REDGreen Green GreenGreen Green
Goal0.40.50.70.40.40
Actual0.50.50.30.10.50.1
Green Green Red Red Green Gray

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top