Conditional formatting based on other column

0
votes

I have a table, that consists of 2 columns. I need to highlight values in first column, that are more or equal than 10% of the number in the second column.

I created additional row there, to clarify, but I can't use it in the formula, tho.

I need to use relative formula, since I am applying filter to these rows, so I need this all not to fall apart, if I am sorting table via filters.

Look of the formatted table

I have looked through this topic: Excel Conditional Formatting based on Other Column , but I couldn't apply it. Or I failed at applying it.

I have tried to apply to the range (of =$A$2:$A$8)following formulas (in conditional formatting "Use a formula to determinate..."):

  • =$A2*100/$B2>=10
  • =ADRESS(ROW(),COLUMN())*100/ADRESS(ROW(),COLUMN()+1)>=10

But none of them did the trick. Can someone help me with this one, please? Thanks.

1
excelconditional-formatting

1 Answers

0
votes

You misunderstand how conditional formatting works.

"I need to use relative formula ..."

No. You don't.

Conditional formatting, although I have never seen it stated anywhere, uses array-based formula. As such you describe the usage of the first instance in the (unfiltered) table, tell it the area of the table, and it will calculate the necessary relative addressing by itself. You do not tell it the relative addressing. Why it behaves like that, I can't tell you.

The formula will then maintain itself throughout any filtering or sorting you carry out. You do not need to account for filtering and sorting - provided, of course, you set everything up on the unfiltered table to begin with.

Conditional Format

Page table

You can see from the attached screenshots, simply using your formula of =$A2*100/$B2>=10 and applying it to $A$2:$A$8 worked perfectly.