Conditional Formating - % Formula

[Bastian Zamorano Cid]

Hello,

I'm having a little struggle with something that I think it can't be so hard.

I have two blocks of 6 columns each. What I want to do is to make a rule for the first block.

I want to color the first block depending on different conditions.

If the division of the first column of the first block between the first column of the second block is between 0 and 15% i want to color it red, and the other rules are similar.

How can I do this formula/rule and automate it for the rest of the block?

Thank you in advance 

[#2 · Accepted solution]

Hello,

I'm not quite sure I understand your requirement completely. For the first column, fine. But the second column, should that compare the first column of the blocks, i.e. take the same color as the first column? Or do you want the second, third, etc. columns from the first block to be compared with the second, third, etc columns from the second block?

Consider this screenshot:

In the upper half, the conditional formatting formula starts in cell B3. It colors the whole row of the first block and divides the first columns of the blocks.  The formula is 

 =AND($B3/$I3>0,$B3/$I3<=0.15)

... with a fixed reference on columns B and I, so these don't change when the format is applied to the second, third, etc. columns.

In the lower half of the screenshot, the formula uses relative references, so each column will be divided by its counterpart in the other block, i.e. column B with I, column C with J, column D with K and so on. The formatting starts in cell B23 and uses the formula

 =AND(B23/I23>0,B23/I23<=0.15)

Does that help with your query?

[#3]

Hi,

Assume that

1. First column of Block 1 in A Column

2. First column of Block 2 in F Column

Select your range in A Column and apply the following.

Conditional Formatting -> New Rule -> Use Formula to determine .... 

Apply the formula

=AND(A1/F1>0,A1/F1<15)

Use Format Painter and drag to other 5 column. Thank you.

[#4]

@Logaraj Sekar, I've already posted that approach. You only repeat it, but with a formula that does not use percentage at all. I don't quite see the point.

[#5]

Thank you very much!
This is what I was searching!

[#6]

Thanks!!

[#7]

What if I want my entire row (A1-D1), mostly text, highlighted when I put and x in the last column(E1)?

[#8]

@Stephanie Jensen

• Select the range to apply the format to, for example A1 to D10.
• Create a new format with a formula
• =$E1="x"
• Select a format and confirm all dialogs.

Note how in the formula the column letter is preceded by a $ sign. That makes the column reference absolute, i.e. regardless where the formula is evaluated, it will always look at column E.

The row number, however, does not have a $ sign. The row reference is relative. Relative to the row that was the active cell when the formula was first entered.

That is important.  If the active cell is A1 and the formula references $E1, then the formula will always look at the same row.

If the If the active cell is A3 and the formula references $E1, then the formula will always look at a cell two rows above. Such scenarios exist, too, but in most cases it's probably a comparison with values in the same row, so pay attention the active cell of a selection when creating a conditional formatting formula.