Circular Equation? wrap on 5 business days..


G

Guest

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.. :)
 
Ad

Advertisements

G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top