Auto Adjusting moving average, please assist

[pacofflin]

Hello everyone, I'm a day trader and would like to make a formula that adjusts a moving average with just one input change. I'm trying to get the average of the last X amount of days and change this based on the input of Days in test. I also want it to test only the number of days in test all the way down. Formula I have now only updates the currently selected number of days and 1 prior. Not sure what I've done wrong or how to fix it. I've been struggling with this for a few days now and would really appreciate some help. Thanks in advance.

=IF($I$2=G2;AVERAGE($E$2:E2);IF($I$2=G2-1;AVERAGE($E$2:E2);""))

[TABLE="width: 500"]
<tbody>[TR]
[TD]

1987.11.25	134.75	135	134.4	134.6		1		4
1987.11.26	134.79	134.8	134.35	134.43		2
1987.11.27	134.55	134.6	133.4	133.4		3
1987.11.30	132.7	133.2	132	132.4		4
1987.12.01	132.4	133.5	132.1	133.45	133.7075	5
1987.12.02	133.4	134	132.45	132.6	131.99	6
1987.12.03	132.55	133.2	131.95	132.95		7
1987.12.04	133	133.75	131.95	132.65		8
1987.12.07	132.9	133.15	132.5	133.1		9
1987.12.08	133.1	133.4	132.65	132.7		10
1987.12.09	132.7	132.7	132.05	132.25		11
1987.12.10	132.2	132.35	129.05	129.25		12
1987.12.11	129.2	129.25	128.2	128.35		13
1987.12.14	128.65	128.65	127.5	128.1		14
1987.12.15	128.1	128.25	126.85	127.7		15
1988.01.04	120.9	123.35	120.3	123.25		16
1988.01.05	123.25	128.45	122.95	128.35		17
1988.01.06	128.3	131.9	126.8	129.45		18
1988.01.07	129.55	130.75	127.75	130.4		19
1988.01.08	130.35	130.4	128.25	128.3		20
1988.01.11	128.4	128.9	127.85	128.55		21
1988.01.12	128.6	128.6	127.15	127.45		22
1988.01.13	127.4	127.6	125.5	127.35		23
1988.01.14	127.35	127.35	126.05	126.1		24
1988.01.15	126.1	131.25	126.1	131.15		25
1988.01.18	131.1	131.9	129.5	129.8		26
1988.01.19	129.8	129.95	128.3	128.6		27
1988.01.20	128.55	129.05	127.15	127.3		28
1988.01.21	127.3	127.9	126.55	127.3		29
1988.01.22	127.3	128.05	127.05	127.25		30
1988.01.25	127.5	128.15	127.4	128.1		31
1988.01.26	128.1	128.3	127.4	127.7		32
1988.01.27	127.6	128.2	126.95	127.15		33
1988.01.28	127.1	127.4	126.9	127.1		34
1988.01.29	127.1	128.1	127.05	128.1		35
1988.02.01	128.35	129.65	128.3	129.4		36
1988.02.02	129.4	129.4	128.2	128.3		37
1988.02.03	128.25	128.5	127.75	128.35		38
1988.02.04	128.3	129.2	127.9	128		39
1988.02.05	128	129.5	127.8	129.45		40
1988.02.08	129.5	129.65	128.55	128.9		41
1988.02.09	128.9	129.4	128.8	128.85		42

<colgroup><col width="86"><col width="109"><col width="104"><col width="102"><col width="110"><col width="120"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86, bgcolor: #99FFFF"] Date [/TD]
[TD="width: 109, bgcolor: #99FFFF"] USD/JPY Open [/TD]
[TD="width: 104, bgcolor: #99FFFF"] USD/JPY High [/TD]
[TD="width: 102, bgcolor: #99FFFF"] USD/JPY Low [/TD]
[TD="width: 110, bgcolor: #99FFFF"] USD/JPY Close [/TD]
[TD="width: 120, bgcolor: #99FFFF"] Moving Average [/TD]
[TD="width: 86, bgcolor: #99FFFF"] Days [/TD]
[TD="width: 86"][/TD]
[TD="width: 86, bgcolor: #99FFFF"] Days in test [/TD]

</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pacofflin]

What it should look like is this, just an example of the what the numbers should be if the formula was working correctly. It's currently taking a 4 day moving average of the [E] USD/JPY Close column.

1987.11.25	134.75	135	134.4	134.6		1		4
1987.11.26	134.79	134.8	134.35	134.43		2
1987.11.27	134.55	134.6	133.4	133.4		3
1987.11.30	132.7	133.2	132	132.4		4
1987.12.01	132.4	133.5	132.1	133.45	133.7075	5
1987.12.02	133.4	134	132.45	132.6	133.42	6
1987.12.03	132.55	133.2	131.95	132.95	132.9625	7
1987.12.04	133	133.75	131.95	132.65	132.85	8
1987.12.07	132.9	133.15	132.5	133.1	132.9125	9
1987.12.08	133.1	133.4	132.65	132.7	132.825	10
1987.12.09	132.7	132.7	132.05	132.25	132.85	11
1987.12.10	132.2	132.35	129.05	129.25	132.675	12
1987.12.11	129.2	129.25	128.2	128.35	131.825	13
1987.12.14	128.65	128.65	127.5	128.1	130.6375	14
1987.12.15	128.1	128.25	126.85	127.7	129.4875	15
1988.01.04	120.9	123.35	120.3	123.25	128.35	16
1988.01.05	123.25	128.45	122.95	128.35	126.85	17
1988.01.06	128.3	131.9	126.8	129.45	126.85	18
1988.01.07	129.55	130.75	127.75	130.4	127.1875	19
1988.01.08	130.35	130.4	128.25	128.3	127.8625	20
1988.01.11	128.4	128.9	127.85	128.55	129.125	21
1988.01.12	128.6	128.6	127.15	127.45	129.175	22
1988.01.13	127.4	127.6	125.5	127.35	128.675	23
1988.01.14	127.35	127.35	126.05	126.1	127.9125	24
1988.01.15	126.1	131.25	126.1	131.15	127.3625	25
1988.01.18	131.1	131.9	129.5	129.8	128.0125	26
1988.01.19	129.8	129.95	128.3	128.6	128.6	27
1988.01.20	128.55	129.05	127.15	127.3	128.9125	28
1988.01.21	127.3	127.9	126.55	127.3	129.2125	29
1988.01.22	127.3	128.05	127.05	127.25	128.25	30
1988.01.25	127.5	128.15	127.4	128.1	127.6125	31
1988.01.26	128.1	128.3	127.4	127.7	127.4875	32
1988.01.27	127.6	128.2	126.95	127.15	127.5875	33
1988.01.28	127.1	127.4	126.9	127.1	127.55	34
1988.01.29	127.1	128.1	127.05	128.1	127.5125	35
1988.02.01	128.35	129.65	128.3	129.4	127.5125	36
1988.02.02	129.4	129.4	128.2	128.3	127.9375	37
1988.02.03	128.25	128.5	127.75	128.35	128.225	38
1988.02.04	128.3	129.2	127.9	128	128.5375	39
1988.02.05	128	129.5	127.8	129.45	128.5125	40
1988.02.08	129.5	129.65	128.55	128.9	128.525	41
1988.02.09	128.9	129.4	128.8	128.85	128.675	42

<colgroup><col width="86"><col width="109"><col width="104"><col width="102"><col width="110"><col width="120"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
[TD="width: 86, bgcolor: #99FFFF"] Date [/TD]
[TD="width: 109, bgcolor: #99FFFF"] USD/JPY Open [/TD]
[TD="width: 104, bgcolor: #99FFFF"] USD/JPY High [/TD]
[TD="width: 102, bgcolor: #99FFFF"] USD/JPY Low [/TD]
[TD="width: 110, bgcolor: #99FFFF"] USD/JPY Close [/TD]
[TD="width: 120, bgcolor: #99FFFF"] Moving Average [/TD]
[TD="width: 86, bgcolor: #99FFFF"] Days [/TD]
[TD="width: 86"][/TD]
[TD="width: 86, bgcolor: #99FFFF"] Days in test [/TD]

</tbody>