The result is differed for same formula in conditional format and normal cell

0
votes

In attached excel sheet, the cell P11 has the below formula in conditional format rule.

Actual formula: =AND($B8="Med Risk",H$5>=$E8,H$5<=$E8+$F8-1).
Conditional format formula for P11 cell is: =AND($B11="Med Risk",H$5>=$E11,H$5<=$E11+$F11-1)

B11---> Med Risk
H5--->  7/8/2019
E11---> 7/15/2019
F11---> 4

B11="Med Risk" ---> True
H5>=E11 --->    False
H5<=E11+F11-1 --->  True
Final result will be ---> False

When the above is used directly in any cell, it gives result FALSE. But it gives result as TRUE when it is used as conditional format formula for a cell. So that P11 cell is filled with format (violet) color. Please let me know, what actually happening here for both cases? If above formula gives result as False always, why the conditional format is applied in P11 cell?

Please help me to understand this.

Excel sheet link: https://drive.google.com/file/d/1rd9yOAcmy0ZyTDkOO3TgHAed7tVdvLfy/view?usp=sharing

Thanks,

1
excelexcel-formula
The first formula is referencing different cells, can we see those inputs as well? Because right now while the formula is the same, the inputs may not be. - Mark S.

1 Answers

2
votes

Certainly!

When relying on the formula you had provided, the result is false. However, in reality, at P11, the formula that conditional formatting is calculating would be =AND($B11="Med Risk",O$5>=$E11,O$5<=$E11+$F11-1)

B11="Med Risk" ---> True

O5>=E11 --->    True

O5<=E11+F11-1 --->  True

Final result will be True.

Since there is no "$" in front of the "H" in your formula, it will change columns in the formatting array.