If 3 cells in a row have a certain value, then.

[anxoperez]

Is there a way to have an if-statement such as the following?

If 3 adjacent cells within a column have a value < -25, then...


thanks

 

[SteveO59L]

=IF(+A1+A2+A3<25,"Check",""), copied corresponding to your range

 

[anxoperez]

I'm not sure I understand your answer.

I mean ANY three adjacent cells.

Maybe I just don't understand what your formula actually means

 

[SteveO59L]

A1, A2 & A3 are adjacent

 

[Jonmo1]

to test the range A1:A100 if ANY 3 consecutive cells are <-25

=SUMPRODUCT(--(A1:A98<-25),--(A2:A99<-25),--(A3:A100<-25))>0

Returns TRUE or FALSE

 

[anxoperez]

Could you explain a bit more?

 

[Jonmo1]

Notice the 3 ranges are same size, but each offset by 1 row.

it reads the formulas 1 row at a time....

if A1 and A2 and A3 are all <-25 it is counted
if A2 and A3 and A4 are all <-25 it is counted
if A3 and A4 and A5 are all <-25 it is counted
if A4 and A5 and A6 are all <-25 it is counted
etc...
Then if the total count is greater than 0 (at least 1) it is TRUE

 

[anxoperez]

It took me a long time, but finally I have understood it.

Had you seen that before or did you just come up with it?

I think it is extremely clever.

And it does work very well!

So that's done. But, the thing is, I need to do more than that. I need to identify those FIRST 3 cells which contain a value < -25 and then do something with the rest of the list (delete it).

How could I use what you have shown to do that?

 

[Jonmo1]

Never saw anything exactly like that before, but the general concept has been used before...

But, the thing is, I need to do more than that

That is why it is good to post your ACTUAL question in the first place.
The solution I provided only identifies if the condition exists. I don't see anyway to identify WHERE in the Range it occurs..That's an entirely different objective.

given the data starts in A1
put this in B3
=IF(AND(A1<-25,A2<-25,A3<-25,COUNTIF(B$1:B2,"X")=0),"X","")

Fill down

an X will be placed in column B next to the 3rd consecutive value in A is <-25

 

[anxoperez]

Well, the thing is, when I have explained the entire issue, I have received no responses. you can check under my name to see how many threads are unanswered.

I haven't tried your suggestion yet (I will in a second). But if it works, that would mean it would type an X when it finds those three cells, right. So, in order to get where I want to go with this, i guess I would just have to then do a new if-statement so that whenever excel finds the first X (I hope it naturally seeks my value in a downward motion) I can then get it to delete everything els, right?