Conditional format formula

[Brew]

In range da20:da106, I have records in each cell with either -450 or 900 value. How do I create a conditional format formula to identify each cell within this range that has a record of 900, which immediately follows 2 or more consecutive -450 records in the range?

Example
Da20=900
Da21=-450
Da22=-450
Da23=-450
Da24=900
Da25=-450
Da26=-450
Da27=900
Da28=900
In this example, only da24 and da27 meet the condition

 

[barry houdini]

Hi Brew, where've you been?

If you select your range (DA20:DA106) with DA20 active cell then apply this formula within conditional formatting

=(DA20=900)*(SUM(DA18:DA20)=0)

 

[Brew]

Yep, that is it... Thanks Barry.
Hey, it's good to be missed.
I had 2 take a break from my creative developments in this area.

dK

 

[Brew]

When I changed the requirement to where the record can be either -465 or 900, rather than -450 or 900, the conditional format formula does not execute when the condition is a match.

 

[Yogi Anand]

ADVERTISEMENT

Hi Brew:

Select the range starting from cell DA20 down, and use the following Conditional Formatting formula ...

=AND(DA20=900,COUNTIF(DA18:DA19,-450)=2)

 

[Brew]

I forgot to mention a change, DA20=4,600, however every other value in the range is either 900 or -465
so, I followed your instruction with the range, starting with DA21 instead of DA20, with the following modification to the formula:
=AND(DA21=900,COUNTIF(DA19:DA20,-465)=2)
I thought it should have worked. It is 2nd condition in each cell's format. However, it still does not execute anymore. The 1st condition is if cell value is equal to -465, which works fine.

 

[Yogi Anand]

ADVERTISEMENT

Hi Brew:

How about restating the complete problem in one setting without reference to other posts so that we can get a clear and unamiguous interpretation in one go ... and take it from there.

 

[Brew]

Restating the problem
In range da21:da106, I have records in each cell with either -465 or 900 value. How do I create a conditional format formula to identify each cell within this range that has a record of 900, which immediately follows 2 or more consecutive -465 records in the range?

Example
Da20=4,600
Da21=-465
Da22=-465
Da23=-465
Da24=900
Da25=-465
Da26=-465
Da27=900
Da28=900
In this example, only da24 and da27 meet the condition

 

[jindon]

Hi

Isn't it simply

Select
DA22:DA106

CF formula =

=And(DA22=900,DA21=-465,DA220=-465)

?

 

[Brew]

I think there is an error with the formula
=And(DA22=900,DA21=-465,DA220=-465)
there should not be a DA220 reference in the formula
so should the formula be
=And(DA22=900,DA20=-465,DA21=-465) ?