# Converting my Excel ideas to AFL. HELP required

Hello all,

I am trying to build a swing chart indicator. I have some excel knowledge which helps me work out how I need to construct arrays. BUT I have trouble converting those ideas into AFL.
Please see the excel attached and my attempt at turning it to AFL.

``````UP = (High > Ref(CUHI,-1)) AND (Low >= Ref(CULO, -1));
DN = (Low < Ref(CULO, -1)) AND (high <= Ref (CUHI, -1));
IN = ( Low >= Ref(CULO,-1)) AND (High <= Ref(CUHI, -1));
OU = (High > Ref (CUHI,-1)) AND (Low < Ref(CULO, -1));

CODE = (UP + DN + IN + OU) ;
CUHI = IIf(CODE == 3, Ref( CUHI, -1), High);
CULO = IIf(CODE ==3, Ref(CULO, -1), Low);

DBQ = IIf(CODE <3, CODE, 0);
ST = IIf( DBQ ==0, Ref(ST,-1),DBQ);

STCH = IIf( ST == Ref(ST,-1),1,0);
HIPL = IIf(( Ref(STCH,1) == 1) AND (Ref(ST,1) ==2),CUHI,Null);
LOPL = IIf(( Ref(STCH,1) == 1) AND (Ref(ST,1) ==1),CULO,Null);

SSC = HIPL + LOPL;

Plot(SSC, "SSC", colorBlue, styleLine, 0,0,0,0,0);```

cheers,`````` @smedlex your formula will not execute since you are referencing some variables before defining them.
(By the way, to get Up, Dn, In, Ou arrays with values 1, 2, 3, 4 you must multiply the resulting logic arrays - that based on your conditions will be filled with either 1 or 0 - times 1, 2, 3, 4)

In any case, I think it would be more likely to receive an answer if you found a way to upload your Excel sample file to a shareable resource and provide a link to download it (AFAIK, MS Office file upload is not allowed in this forum).
Alternatively, try to explain in detail all the steps of your calculations (no AFL code).

Thanks for the tips Beppe,
here is the revised formula.

``````
UP = (High > Ref(CUHI,-1)) AND (Low >= Ref(CULO, -1));
DN = ((Low < Ref(CULO, -1)) AND (high <= Ref (CUHI, -1))) *2;
IN = (( Low >= Ref(CULO,-1)) AND (High <= Ref(CUHI, -1))) *3;
OU = ((High > Ref (CUHI,-1)) AND (Low < Ref(CULO, -1))) *4;

CODE = (UP + DN + IN + OU) ;
CUHI = IIf(CODE == 3, Ref( CUHI, -1), High);
CULO = IIf(CODE ==3, Ref(CULO, -1), Low);

DBQ = IIf(CODE <3, CODE, 0);
ST = IIf( DBQ ==0, Ref(ST,-1),DBQ);

STCH = IIf( ST == Ref(ST,-1),1,0);
HIPL = IIf(( Ref(STCH,1) == 1) AND (Ref(ST,1) ==2),CUHI,Null);
LOPL = IIf(( Ref(STCH,1) == 1) AND (Ref(ST,1) ==1),CULO,Null);

SSC = HIPL + LOPL;

Plot(SSC, "SSC", colorBlue, styleLine, 0,0,0,0,0);

``````

Here is a hyperlink to my excel file. My swing chart