How to reference cell in formula where result met condition

Question

Is there a way to write a formula for Variation so that it always relates to the lastest cell where the Variation was greater than a threshold?

In the following table the denominator of the percentage changes if the absolute value of Variation is greater than 10%. The formulas were changed manually by me.

------------------------------------------
| Row | Value  | Variation| Formula      |
------------------------------------------
|   1 | 1,1608 |   0,0%   |  A2/ A$2 - 1 |
|   2 | 1,1208 |  -3,4%   |  A3/ A$2 - 1 |
|   3 | 1,0883 |  -6,2%   |  A4/ A$2 - 1 |
|   4 | 1,0704 |  -7,8%   |  A5/ A$2 - 1 |
|   5 | 1,0628 |  -8,4%   |  A6/ A$2 - 1 |
|   6 | 1,0378 | -10,6%   |  A7/ A$2 - 1 | <---- Abs. Variation > 10 %
|   7 | 1,0353 |  -0,2%   |  A8/ A$7 - 1 | <---- Change denominator
|   8 | 1,0604 |   2,2%   |  A9/ A$7 - 1 |
|   9 | 1,0501 |   1,2%   | A10/ A$7 - 1 |
|  10 | 1,0706 |   3,2%   | A11/ A$7 - 1 |
|  11 | 1,0338 |  -0,4%   | A12/ A$7 - 1 |
|  12 | 1,0110 |  -2,6%   | A13/ A$7 - 1 |
|  13 | 1,0137 |  -2,3%   | A14/ A$7 - 1 |
|  14 | 0,9834 |  -5,2%   | A15/ A$7 - 1 |
|  15 | 0,9643 |  -7,1%   | A16/ A$7 - 1 |
|  16 | 0,9470 |  -8,7%   | A17/ A$7 - 1 |
|  17 | 0,9060 | -12,7%   | A18/ A$7 - 1 | <---- Abs. Variation > 10 %
|  18 | 0,9492 |   4,8%   | A19/A$18 - 1 | <---- Change denominator
|  19 | 0,9397 |   3,7%   | A20/A$18 - 1 |
|  20 | 0,9041 |  -0,2%   | A21/A$18 - 1 |
------------------------------------------

Is it possible to write a formula where the denominator changes on a given condition?

All my attempts with Array formulas, MATCH, AGGREGATE etc. went to nowhere.

Answer 1

Here is another way:

Place zero in E2.

In E3:

=IF(E2<-0.1,B3/B2-1,B3*(E2+1)/B2-1)

So what I'm trying to do is to work out the denominator from the previous row. So

E2=B2/denominator-1

Re-arranging you get

Denominator=B2/(E2+1)

So in the regular case you divide by this denominator, otherwise you divide by B2.