Conditional formatting Question

[Lmaonade]

Hi guys,

Got a problem. I've not used conditional formatting much before and unsure how to get it to work properly for my particular situation.

I've got these cells

I want the conditional formatting to give me a data bar for whatever number is in the days covered column compared to the holiday days.

For example the first one is 20 days holiday, 10 days covered. So the bar is in the middle.

Now this cell is set as: Minimum number 0, maximum number =K$4$, Now if I copy this down it will always use cell K4, rather than using cell K5 for L5 and K6 for L6 etc.

So for example. Cell L5 will be showing how many days covered using Cell K4 as a maximum number rather than cell K5.

I just want to create one rule for the whole column to show the number of days covered compared to the holiday days.

I hope that made sense.

Any help would be appreciated.

Thanks,
Andy

 

[Norie]

Andy

All you should need to do is change =$K$4 to $K4, i.e. remove the $ before the row number.

 

[Lmaonade]

Hi Norie,

Thanks for the reply.

When I try this. It comes up with an error: "You cannot use relative references in Conditional Formatting criteria for colour scales, data bars and icon sets"

Am I doing something wrong?

 

[Lmaonade]

 

[MartyS]

Hi guys,

Got a problem. I've not used conditional formatting much before and unsure how to get it to work properly for my particular situation.

I've got these cells

I want the conditional formatting to give me a data bar for whatever number is in the days covered column compared to the holiday days.

For example the first one is 20 days holiday, 10 days covered. So the bar is in the middle.

Now this cell is set as: Minimum number 0, maximum number =K$4$, Now if I copy this down it will always use cell K4, rather than using cell K5 for L5 and K6 for L6 etc.

So for example. Cell L5 will be showing how many days covered using Cell K4 as a maximum number rather than cell K5.

I just want to create one rule for the whole column to show the number of days covered compared to the holiday days.

I hope that made sense.

Any help would be appreciated.

Thanks,
Andy

Excel 2010

	A	B	C	D	E	F
Data Bars

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Holiday Days[/TD]
[TD="align: center"]Days Covered[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Calc[/TD]
[TD="align: center"]Formula[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50%[/TD]
[TD="align: center"]=$C3/$B3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]33%[/TD]
[TD="align: center"]=$C4/$B4[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50%[/TD]
[TD="align: center"]=$C5/$B5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]75%[/TD]
[TD="align: center"]=$C6/$B6[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]40%[/TD]
[TD="align: center"]=$C7/$B7[/TD]

</tbody>

Sheet3​

If I understand you correctly, you're wanting Data Bars to appear that show what fraction/percentage of [Days Covered] is of [Holiday Days].

So, for the first row (20 HD and 10 DC) 10 days out of 20 is half. The bar appears in the middle.
For the next row (6 HD and 2 DC) only 2 of the 6 days were covered which is 2/6 or 1/3 = 33%
and so on.

I don't think you can create the data bars in the [Days Covered] column because Data Bars MUST (it seems) relate to the column they appear in.

You CAN however put the data bars in the column next to the [Days Covered] field, as shown in the image above.

To do this, enter the formula =B2/A2 in cell C2 and copy down.


Next, highlight the cells containing the formula you've just entered and select Conditional Formatting: Data Bars: More Rules

Set MINIMUM to NUMBER and put the value as 0 (zero)
Set MAXIMUM to NUMBER and put the value as 1 (one)

These represent 0 to 100% (0=0%, 1=100%)


Select the colour you want. Click on OK

This way, all data bars will now appear consistent.


Keeping the cells highlighted, now go back to Conditional Formatting and select Manage Rules

There should be only one rule showing. Click on Edit Rule..

You'll see the Show Bar Only checkbox. Put a check in the box so the calculations don't appear (as shown below)

Hope that helps.


(If anyone knows how to get Data Bars to appear using data from a different cell-range, that'd be awesome!!)

 

[Lmaonade]

Hi Marty,

That actually works great. Thank you very much for that

 

[MartyS]

Great! :D Glad it helped.