Make a cell bold based on a percentage lower than the next lowest cell

[m5rcc]

Hello all,

Maybe the title is a bit ambiguous but I'll try and break it down.

I have a set of data, which so far is 100+ rows.

I have 6 cells say ranging from A1:A6. I have used Conditional Formatting on that row of data so that it fills a colour when it has the lowest value cell. There may be instances when there is more than one cell that has the lowest value.

Whilst that objective has been achieved can anyone shed light on how I can make, with the same formatted row of data, a formula or even a new conditional formatting rule that allows me to make bold a cell that is say 15% lower than the next lower cell. It might be that I need a formula of some sort that then needs to be cross-referred but I don't know how to do it in a clean way.

So for example, I would have:

[TABLE="width: 383"]
<tbody>[TR]
[TD="class: xl63, width: 46"]20[/TD]
[TD="class: xl63, width: 79"]27[/TD]
[TD="class: xl63, width: 85"]26[/TD]
[TD="class: xl63, width: 51"]22[/TD]
[TD="class: xl63, width: 53"]27[/TD]
[TD="class: xl63, width: 69"]21[/TD]
[/TR]
</tbody>[/TABLE]


20 is highlighted being the lowest, but it would not be made bold cos its only 4.8 odd % less than 21 (the second lowest cell).

However:

[TABLE="width: 450"]
<tbody>[TR]
[TD="class: xl65, width: 46"]25[/TD]
[TD="class: xl65, width: 79"]30[/TD]
[TD="class: xl65, width: 85"]30[/TD]
[TD="class: xl65, width: 51"]30[/TD]
[TD="class: xl65, width: 53"]30[/TD]
[TD="class: xl65, width: 69"]31[/TD]
[TD="class: xl65, width: 67"]30[/TD]
[/TR]
</tbody>[/TABLE]


25 is highlighted the lowest, but it should also be made bold as its over 15% less than the next lowest of 30.

Any help would be greatly appreciated.

Thanks in advance!

 

[Tetra201]

Try this:

Rule 1 (fill only) =A$1=MIN($A$1:$G$1)

Rule 2 (fill and bold) =AND(A$1=MIN($A$1:$G$1),(SMALL($A$1:$G$1,2)-SMALL($A$1:$G$1,1))/SMALL($A$1:$G$1,2)>15%)

 

[m5rcc]

Try this:

Rule 1 (fill only) =A$1=MIN($A$1:$G$1)

Rule 2 (fill and bold) =AND(A$1=MIN($A$1:$G$1),(SMALL($A$1:$G$1,2)-SMALL($A$1:$G$1,1))/SMALL($A$1:$G$1,2)>15%)

So this will be as a new rules done via Conditional Formatting>Manage Rules>New rule>Use a formula to determine which cells to format ?

 

[Tetra201]

Yes, and you have to enter both rules.

 

[m5rcc]

Yes, and you have to enter both rules.

Thanks

How do I replicate (with ease) to each row, i.e. A1:A6, B1:B6, C1:C6, etc?

 

[Tetra201]

To replicate the conditional formatting to row 2, 3, etc., do the following
(caveat: the column rage should remain the same for each row):

- Apply the following two updated rules to range A1:G1
=A1=MIN($A1:$G1) fill only
=AND(A1=MIN($A1:$G1),(SMALL($A1:$G1,2)-SMALL($A1:$G1,1))/SMALL($A1:$G1,2)>15%) fill and bold

- Select the entire row 1, click Format Painter

- Select the entire row 2 and keep the left mouse button pressed, then drag down as needed

 

[m5rcc]

To replicate the conditional formatting to row 2, 3, etc., do the following
(caveat: the column rage should remain the same for each row):

- Apply the following two updated rules to range A1:G1
=A1=MIN($A1:$G1) fill only
=AND(A1=MIN($A1:$G1),(SMALL($A1:$G1,2)-SMALL($A1:$G1,1))/SMALL($A1:$G1,2)>15%) fill and bold

- Select the entire row 1, click Format Painter

- Select the entire row 2 and keep the left mouse button pressed, then drag down as needed

Thanks - sorry to keep adding issues, but, the "fill and bold" does not work when there is, for example, more than one low figure:

[TABLE="width: 450"]
<tbody>[TR]
[TD="class: xl63, width: 46"]-[/TD]
[TD="class: xl63, width: 79"]-[/TD]
[TD="class: xl63, width: 85"]25[/TD]
[TD="class: xl63, width: 51"]35[/TD]
[TD="class: xl63, width: 53"]-[/TD]
[TD="class: xl63, width: 69"]-[/TD]
[TD="class: xl63, width: 67"]25[/TD]
[/TR]
</tbody>[/TABLE]

Both the 25s should be filled and bold.

 

[Tetra201]

Try these updated rules:

Rule 1 (fill only)

=A1=MIN($A1:$G1)

Rule 2 (fill and bold)

=AND(A1=MIN($A1:$G1),(LARGE($A1:$G1,COUNTIF($A1:$G1,">"&MIN($A1:$G1)))-MIN($A1:$G1))/LARGE($A1:$G1,COUNTIF($A1:$G1,">"&MIN($A1:$G1)))>15%)

 

[m5rcc]

Try these updated rules:

Rule 1 (fill only)

=A1=MIN($A1:$G1)

Rule 2 (fill and bold)

=AND(A1=MIN($A1:$G1),(LARGE($A1:$G1,COUNTIF($A1:$G1,">"&MIN($A1:$G1)))-MIN($A1:$G1))/LARGE($A1:$G1,COUNTIF($A1:$G1,">"&MIN($A1:$G1)))>15%)

Perfect! Many thanks!

 

[Tetra201]

You are welcome.