Formula to Fire When Last 10 Values Are Within 0.03

[rob51852]

Hi guys,

I have a list of data in column A which updates automatically with new values being inserted in the next row in the column.

Is there a formula that will return the word "stable" when the last 10 values are within 0.03 of each other?

For example, the last 10 values may fluctuate between 2.50 and 2.52 and it is in situations like these that I would like the formula to fire.

Thanks

 

[J.Ty.]

Hi,

The following is the first approximation. It returns an error when there are less than 10 values in column A. It can be changed when you tell me what the result should be then.

Book1
	A	B
1	1	STABLE
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	10
12	10
13	10
14	10.01
15	10
16	10
17	9.99
18	9.99
19	10
Sheet3

Cell Formulas
Range	Formula
B1	B1	=IF(ABS(MAX(OFFSET($A$1,COUNTA($A:$A)-10,0,10,1))-MIN(OFFSET($A$1,COUNTA($A:$A)-10,0,10,1)))<0.03,"STABLE","")

 

[rob51852]

Hi J.Ty.

Thanks. This is great. I'm not bothered about the error.

Thanks again!

 

[J.Ty.]

Please test it carefully if it really does what you want. Please test corner cases. In particular, as of now the formula assumes that all values in column A form a contiguous block, starting in A1 (i.e., there is no header), that there are no blanks among data values, etc.

J.Ty.