Calculate values based on both previous and next rows, in a dynamic way

[Vic6]

Hi guys,

Please help me out with the following topic. I want to find a way to automatically calculate values for variable y2. Variables x and y1 are already calculated.

The rules for calculation of y2 variable are the followings:

1) If y1=-1 (day 3), the value of y1 from the day1 and day2=0 and in the next day (day 4) y1=0 then the counter for y2 (day 3) is -1.

2) If there is no counter reset (i.e. y1 from day 16=-2 and y1 from day 18=-1) then y2 from day 18=-4. In day 19, y2 should be -8 as there still is no counter reset. Same with day 21, when y2 should be -16.

The counter resets only in the following conditions:

- if y1 from previous day and y1 from next day =0 (e.g. day2=0, day3=-1 and day4=0 -> day 5 counter reset);

- if y1 from next two days=0 (e.g day5=-1, day6=-2, day7=0, day8=0 ->in day9 counter reset).

Day_no	x	y1	y2
1	1	0	0
2	1	0	0
3	2	-1	-1
4	1	0	0
5	4	-1	-1
6	3	-2	-2
7	1	0	0
8	1	0	0
9	1	0	0
10	1	0	0
11	2	-1	-1
12	1	0	0
13	5	0	0
14	6	0	0
15	7	-1	-1
16	2	-2	-2
17	1	0	0
18	6	-1	-4
19	2	-2	-8
20	1	0	0
21	3	-1	-16
22	1	0	0
23	1	0	0
24	5	-1	-1
25	1	0	0

[Kurt_Bremser]

According to your condition #1, y2 in day 21 should be -1 (y1 = -1 and the following y1 = 0).

[Vic6]

It seems to be a mistake in my conditions...The correct value for y2 in day 21 should be -16 because there is no counter reset between day 19 and day 21. The break will take place in day 23 since day 20=0, day 21=-1 and day 22=0.
The condition #1 would apply only in case we would had a day 0 with y1=0.

[Kurt_Bremser]

Then you need to rephrase your conditions until they lead to exactly the outcome that you want, and do not contradict it in places.

See Maxim 42, a well-done question will be 90% of the answer.

[Vic6]

Conditions have been updated in the initial message.