Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Equation? wrap on 5 business days..
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.. :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Circular Equation? wrap on 5 business days..
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.. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
With this business with ONLY 5,- EUR OVER 61,370 EUR in 3 months earn!! | Charts and Charting in Excel | |||
With this business with ONLY 5,- EUR OVER 61,370 EUR in 3 months earn!! | Charts and Charting in Excel | |||
Macro Quandry | Excel Discussion (Misc queries) | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions | |||
How would I change a date cell to decrease it by business days? | Excel Discussion (Misc queries) |