Conditional formatting to compare a cell to 120 cells

I am new to this forum and to excel, so forgive me if I am using incorrect terminology.

What I want to do: I want to sort through many, many values in a column and need to indicate cell ranges where there is a change less than 1 in the next 120 cells.

For example: Say, I had the value "100.00" ...I need all of the next 120 cell values to be less than "101.00" or greater than "99.00", which is less than a "1.00" change. If that is true, then I would like that cell in the beginning of the range to be highlighted OR just highlight the range entirely. This means that if the next 120 cell values actually went up to "101.01" at a point, then there will be no cells highlighted. Does this make sense?

Based on intuition, is it possible to apply conditional formatting where the cell compares it's value to the next 120 cells INDIVIDUALLY? When I try this formula:

=A1<((A2:A121)+1)

I apply it to all of the cells in the range in the column. HOWEVER, it highlights all of the cells because the function only needs one value that holds true for the formula. I can not have a single value in the next 120 cells be more than a 1.0 change.

Any ideas?!!? Help appreciated! Thanks.

Originally Posted by Glenn Kennedy

Hi there and welcome to the Excel Forum. You can use this formula for CF in A1 and copy formatting as needed:

=(OR(A1>=$D$1+1,A1<=$D$1-1)) which highlights those OUTSIDE the +/- 1 range... or

=(AND(A1>=$D$1-1,A1<=$D$1+1)) which highlights those INSIDE the +/- 1 range

Thanks for getting back to me, Glen. With the specific cell reference ($D$1), is that supposed to be "D" or "A", as I am comparing in the column to A1? Also, this formula needs to apply to 120 cells, and then repeat for each cell down the column. For example, the formula in A1 would be comparing the A1 value to A2, A3, A4, A5...., and then A121. Then repeat the formula for A2 comparing with A3, A4, A5,....A122. Then A3 would be A4....A123....there are thousands of cells down this column, which essentially requires this comparison of 1 cell value to each of the 120 cells below it thousands of times until the last value of the column. Is this possible?

Or what about conditional formatting using a "between" format? The excel only provides one specific value to compare to though...

Originally Posted by Glenn Kennedy

Ah... Do you mean that A1 is compared to the 120 below it, A2 to the 120 below it, etc? If so - then what I have suggested is useless. I suspect that it might be impossible, too. If you have A10 highlighted - how will Excel know if it's different from A9, A8, or any of the cells preceeding it.

If what you want is only to compare A1 to the 120 below it then modify my original suggestion to:

=(OR(A2>=$a$1+1,A2<=$a$1-1)) and copy the formatting down; or

=(AND(A2>=$a$1-1,A2<=$a$1+1))

Thats the idea. But I need the A1 to compare to each cell individually, which is 120 cells after it = 120 comparisons. So it needs to compare to A2, then A3, then A4, all the way to A121. If there are no values that are greater than (A1+1) or less than (A1-1), the A1 cell should return as highlighted. The difficulty arises when I want this same set of rules to apply to the next cell in the column, A2, then A3, all the way through the entire column.