Statistical
Find maximum period-over-period jump, steepest drop & largest absolute swing
Find the largest and smallest signed adjacent-period changes, the largest absolute change, and its first matching period.
Signed extrema and absolute changes answer different questions
Compute each change as the current value minus the previous value, in chronological row order. MAX returns the largest signed change, which can still be negative when every period declines. MIN can be positive when every period increases; it is not automatically a loss.
ABS removes direction so MAX of the absolute changes measures the largest adjacent-period movement. This is neither a percentage change nor a peak-to-trough drawdown or a statistical volatility estimate. The returned label is the ending period of the transition.
XMATCH returns the first source-row match when absolute changes tie. The example requires all five input values to be numeric and all period labels present; do not silently bridge missing periods. Units and spacing between observations should be comparable.
| Month (A) | Metric Value (B) | Signed Delta B_t - B_{t-1} (C) | Abs Swing |Delta| (D) |
|---|---|---|---|
| Jan | 200 | — (Baseline) | — |
| Feb | 215 | +15 | 15 |
| Mar | 260 | +45 (Biggest Gain!) | 45 |
| Apr | 250 | -10 | 10 |
| May | 190 | -60 (Steepest Drop!) | 60 (Largest Shock -> "May"!) |
Ending period of the largest absolute adjacent change
D3:D6 stores =ABS(B3 - B2) so XMATCH locates the exact transition month.
=IF(OR(COUNT(B2:B6)<>5,COUNTBLANK(A2:A6)>0),"Invalid inputs",INDEX(A3:A6, XMATCH(MAX(D3:D6), D3:D6, 0)))Largest signed change
Start C3:C6 on Row 3 (second data period) so Row 2 baseline is never subtracted from header text!
=IF(OR(COUNT(B2:B6)<>5,COUNTBLANK(A2:A6)>0),"Invalid inputs",MAX(C3:C6))Try the example
Use the original values or change them to test your understanding.