How to make a "hierarchical conditional formatting" in Excel 2010?

Question

i need to program a "hierarchical conditional formatting" in Excel 2010, based on two entries: the cell format on the left and whether the value belongs to a given range or not.

To understand better than I need to, suppose I’m in cell B1.

I was able to define the first condition, clicked on by clicking on "Use a formula to determine which cells should be formatted" and typed = CÉL ("format"; A1) = "P1", because I need to know if A1 is or is not a percentage.

Also, I can define the second condition by clicking on "Format only cells that contain" and then on "between", for example, 0.5 and 0.6.

The problem is that the desired range depends on the shape of the cell on the left. So I wanted to define something like this:

If (A1 is percentage) E (B1 is between 0.5 and 0.6), apply the selected format.

If (A1 is not percentage) ED (B1 is between 5 and 6), apply the selected format.

I tried to select "Use a formula to determine which cells should be formatted" and enter the formula below, but it didn’t work!

= E (CÉL ("format"; A1) = "P1"; AND ("B1> 0,5"; "B1 <0,6"))

Can someone help me?

From now on, thank you very much.

Answer 1

Good night buddy, all right?

Check your percentage field, because in some cases depending on the number of decimals, the function (CÉL + Format) can bring you other returns.

See the example of several returns in the image below:

Another point, I believe your parole is incorrect. Try to implement the formula as below:

Complete formula:

=OU(E(OU(CÉL("formato";B7)="P0";CÉL("formato";B7)="P1";);E7>=0,5;E7<=0,6);E(OU(CÉL("formato";B7)="G";CÉL("formato";B7)="F0";CÉL("formato";B7)="F1";);E7>=5;E7<=6))