Formulas to Trigger on Spike in Dynamic Range

[rob51852]

Hi,

I have a spreadsheet with a dynamic range in column A that updates automatically by adding data in the last row. Is it possible to identify “spikes” in the range using formulas in real-time?

For example, in the following range I would like the formulas to return the word “Attention” when there is a spike to a value that is more than 7 from the previous value and then drops in the subsequent value by more than 7.

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[wideboydixon]

The tricky part will be getting the formula to extend for each new entry you add. However, you could just paste down this formula to the entire column if necessary:

Book1
	A	B
1	Data	Spike?
2	2
3	3
4	2
5	3
6	4
7	2
8	2.5
9	3
10	11	Spike
11	2
12	2.5
13	3
14	2.5
15	1
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(IF(AND(ABS(A2-A1)>7,ABS(A2-A3)>7),"Spike",""),"")

WBD

 

[Aladin Akyurek]

Select the current range in A:B including the headers and insert a table (Insert | Table) via the ribbon. The formula in column will be copied down automatically.

 

[rob51852]

Thanks guys. Would it then be possible to have a second formula that triggered if the word spike appeared next to the last data point in column A?