# Thread: Finding ranges of values that satisfy a condition that stops and starts

1. ## Finding ranges of values that satisfy a condition that stops and starts

Hi I have never really used excel for programming and im a little stuck.

My problem; I have a set of data in two columns, I need to search column B for values < -1 or >1 and then register the equivalent range of cells in column A that the condition was satisfied. I tried SUMIF and then I was going to try find an average using COUNTIF but that was no use.

It needs to be able to say conditions were met for A23:A45 and A67:A88 or something like that, better yet would be it outputting the values of the first and last cells of each range that the conditions were satisfied.

Thanks in advance for any help.

Paul

2. Hi
You could put this formula in a new column to test for either value:
=IF(OR(A1<-1,A1>1),"Yes","No")
Then you could filter the rows for the "Yes" result.
Or you could use COUNTIF to count the number of "Yes" results.
If you want to display which cells meet that criteria then you may need VBA to achieve that.
Hope this helps.
Good luck.
Tony