Conditonal Formatting: Explain This Please

[DOUG]

I am trying to repair a command to format conditionally one cell based on the
contents of another cell. Please interpret the following expression.

=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,

DOUG ECKERT

 

[Luke M]

It appears you're only showing part of an expression, or a badly written
formula, but of what shown...

Both of the following criteria must be met to return a TRUE result:
1. There must be a total of 16 numbers in the ranges of H150:J150 and
M150:Y150 (so, the other cells could be blank or text)
2. There can only be 1 or 0 blank cells in the range K150:L150.

 

[DOUG]

Luke: You are right, it is only part of a larger formula. Actually, the
entire thing looks like this...

=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)="FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1))),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60)

It has to do with discerning when people are coming due for training. I
believe it says that if any of the dates displayed are within 60 days of
today, then the little stoplight in the A column will turn red. (In other
rows, some symbols are green, some yellow). There is conditional formatting
in the subsequent date columns too.

DOUG

 

[Luke M]

Most curious. First, there are parts of the formula that are useless/not
needed. For instance:
COUNTBLANK(AA150)<=1
Is only checking one cell. The cell will either be blank (1) or not (0).
either way, condition is true!
....AND(NOT(LEFT(G150,4)="FFZZ"),...
This is included in an OR function that already checks this. No need to
write it twice.

Finally, the "-60" is in the false arguement by itself, not really doing
anything. I'm *assuming* it's supposed to be part of the previous bit.
Simplfied and re-written
=IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K150:L150)<=1,OR(LEFT(G150,4)="FFZZ",COUNT(Z150)=1)),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())-60)

In English:
If there are 16 numbers in first group of ranges, and
there is only at most 1 blank in K150:L150, and
one of the following is true:
(G150 contains "FEZZ" or
there is a number in Z150)

If TRUE:
Then, depending on if G150 contained FEZZ, either subtract today's date from
smallest value in range, or 60 days prior to today from smallest value in
range.

As long as this results in any value not equal to zero, conditional
formatting will be triggered.

If FALSE:
Return a "FALSE" value

 

[DOUG]

Luke: Wow! It sounds very logical and I shall give it a try.

Thank you very much!

DOUG

 

[DOUG]

Luke M: Your revised formula yielded "FALSE". I guess that could be
formatted to produce a red icon symbol, or red circle. But, the icon is
supposed to produce symbols in a range of red, yellow and green. I am not
sure how to do that...
DOUG

 

[Luke M]

You may want to read the help file on conditional formatting for more
details. A conditional format formula is like a switch, either ON or OFF. If
you want 3 different results (red, yellow, green) you would need 3 different
formulas. Because of the 60 in this formula, I'm assuming this formula was
meant to check the "red" condition. Since it is returning false (based on the
data) this is saying that the red light should be off.

Again, the middle part of formula is very odd. It does a subtraction, but it
doesn't compare it to anything. Generally, you want conditional format
formulas to return either TRUE or FALSE. Returning a value is, to say the
least, odd and difficult to interpret.

 

[DOUG]

Thanks again, Luke!

DOUG

You may want to read the help file on conditional formatting for more
details. A conditional format formula is like a switch, either ON or OFF. If
you want 3 different results (red, yellow, green) you would need 3 different
formulas. Because of the 60 in this formula, I'm assuming this formula was
meant to check the "red" condition. Since it is returning false (based on the
data) this is saying that the red light should be off.

Again, the middle part of formula is very odd. It does a subtraction, but it
doesn't compare it to anything. Generally, you want conditional format
formulas to return either TRUE or FALSE. Returning a value is, to say the
least, odd and difficult to interpret.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*