Circular Equation? wrap on 5 business days..

[nastech]

Trying to find a way to use 5 days data, without going back more than 5 days.
I have copy of file can send, or put online. cells are as:

A1: SMA B1: 2.0
A2: REMA B2: 2.0
A3: R penalty B3: 15.0

D1: Symbol E1: dir K1: dir
F1: SMA mo, G1: SMA tu ... J1: SMA fr
F2: =IF(OR($B$1="",R2=0),"",AVERAGE(OFFSET(R2,-$B$1+1,0,$B$1,1)))
(drag across to J2, think correct)

L1: REMA mo... P1: REMA fr
L2: =(V2*(1+2*$B$3)+(2/($B$2+1))*(R2-O2)-$B$3*O2)/(1+$B$3)
M2: =(R2*(1+2*$B$3)+(2/($B$2+1))*(S2-P2)-$B$3*P2)/(1+$B$3)
N2: =(S2*(1+2*$B$3)+(2/($B$2+1))*(T2-M2)-$B$3*L2)/(1+$B$3)
O2: =(T2*(1+2*$B$3)+(2/($B$2+1))*(U2-N2)-$B$3*M2)/(1+$B$3)
P2: =(U2*(1+2*$B$3)+(2/($B$2+1))*(V2-O2)-$B$3*N2)/(1+$B$3)

Problem is, equations must stop at 5 days / 4? and use previous 2 days of
mo-fr for example:

R1: mo ... V1: fr (R2 thru V2 enter 23.00 24.00 25.00 26.00 27.00)

REMA: Regularized EMA:
=(2nd close(1+2*penalty)+(2/x days+1))*(3rd close-rema day2)-penalty*rema
day1)/(1+penalty)

usually, rema day1 (mo) is equal to 1st close, & rema day2 (tu) = 2nd close

a. Problem with REMA section, above: how to stop at 5 days so circular
equation does not occur.
b. Before 5 days, just use previous 2 figures from close prices: mo tu we
th fr

would need to modify all files in columns L thru P, above OR maybe have cell
that tells which day it is today? Thanks in advance..

[nastech]

incidently: F1 thru J1: SMAmo ... SMAfr
J2: =IF(OR($B$1="",R2=0),"",AVERAGE(OFFSET(R2,-$B$1+1,0,$B$1,1))) and
drag accross


"nastech" wrote:

> Trying to find a way to use 5 days data, without going back more than 5 days.
> I have copy of file can send, or put online. cells are as:
>
> A1: SMA B1: 2.0
> A2: REMA B2: 2.0
> A3: R penalty B3: 15.0
>
> D1: Symbol E1: dir K1: dir
> F1: SMA mo, G1: SMA tu ... J1: SMA fr
> F2: =IF(OR($B$1="",R2=0),"",AVERAGE(OFFSET(R2,-$B$1+1,0,$B$1,1)))
> (drag across to J2, think correct)
>
> L1: REMA mo... P1: REMA fr
> L2: =(V2*(1+2*$B$3)+(2/($B$2+1))*(R2-O2)-$B$3*O2)/(1+$B$3)
> M2: =(R2*(1+2*$B$3)+(2/($B$2+1))*(S2-P2)-$B$3*P2)/(1+$B$3)
> N2: =(S2*(1+2*$B$3)+(2/($B$2+1))*(T2-M2)-$B$3*L2)/(1+$B$3)
> O2: =(T2*(1+2*$B$3)+(2/($B$2+1))*(U2-N2)-$B$3*M2)/(1+$B$3)
> P2: =(U2*(1+2*$B$3)+(2/($B$2+1))*(V2-O2)-$B$3*N2)/(1+$B$3)
>
> Problem is, equations must stop at 5 days / 4? and use previous 2 days of
> mo-fr for example:
>
> R1: mo ... V1: fr (R2 thru V2 enter 23.00 24.00 25.00 26.00 27.00)
>
> REMA: Regularized EMA:
> =(2nd close(1+2*penalty)+(2/x days+1))*(3rd close-rema day2)-penalty*rema
> day1)/(1+penalty)
>
> usually, rema day1 (mo) is equal to 1st close, & rema day2 (tu) = 2nd close
>
> a. Problem with REMA section, above: how to stop at 5 days so circular
> equation does not occur.
> b. Before 5 days, just use previous 2 figures from close prices: mo tu we
> th fr
>
> would need to modify all files in columns L thru P, above OR maybe have cell
> that tells which day it is today? Thanks in advance..
>
>
>