Excel formula

[subha120]

ALGO FINAL - Check.xlsm
	A	B	C	D	E	F	G
8	NATURALGAS20JULFUT					#DIV/0!	#DIV/0!
9	NATURALGAS20AUGFUT					#DIV/0!	#DIV/0!
10	NATURALGAS20JULFUT					0	0
11	NATURALGAS20AUGFUT					#NAME?	#NAME?
12	NATURALGAS20JULFUT					0	0
13	NATURALGAS20AUGFUT					#NAME?	#NAME?
14	NATURALGAS20JULFUT					0	0
15	NATURALGAS20AUGFUT					#NAME?	#NAME?
EXCEL SIGNAL GEN.

Cell Formulas
Range	Formula
F8	F8	=IF(ALGO!E24>1.8,"SE ",IF(ALGO!E24<-1.8,"LE","No signal"))
G8:G15	G8	=MyTableStyle[@Column1]
F9	F9	=IF(ALGO!E24>1.8,"SE ",IF(ALGO!E24<-1.8,"LE","No signal"))
F10,F14,F12	F10	=[@LTP]
F11,F15,F13	F11	=IF(F10="LE","SE",IF(F10="SE","LE",NA))

Please help to fill the "F" column with the formula , given at F8 & F9. Have to fill F10 & F11 with same formula as F8 & F9 but changes E24 to E25 in the formula at F10 and F11. Similarly for F12 & F13 and so on.

 

[GraH]

Hi subha120,

I hope I got it right. I understand that each 2nd row is the same formula (thus same result) as the previous row. Each odd row needs to change the reference to the next row starting from ALGO!E24.

Can't figure it out without a helper column, say "H" = [H8] = N(H7)+ISODD(ROW())
Then formula becomes: [F8] = IF(ISODD(ROW()),G7,IF(OFFSET(ALGO!$E$24,H8,,1)>1.8,"SE ",IF(OFFSET(ALGO!$E$24,H8,,1)<-1.8,"LE","No signal")))

 

[Peter_SSs]

See if this, copied down does what you want.

20 07 26.xlsm
	A					F
8	NATURALGAS20JULFUT					SE
9	NATURALGAS20AUGFUT					SE
10	NATURALGAS20JULFUT					No signal
11	NATURALGAS20AUGFUT					No signal
12	NATURALGAS20JULFUT					No signal
13	NATURALGAS20AUGFUT					No signal
14	NATURALGAS20JULFUT					LE
15	NATURALGAS20AUGFUT					LE
EXCEL SIGNAL GEN.

Cell Formulas
Range	Formula
F8:F15	F8	=IF(INDEX(ALGO!E:E,23+ROUNDUP(ROWS(F$8:F8)/2,0))>1.8,"SE ",IF(INDEX(ALGO!E:E,23+ROUNDUP(ROWS(F$8:F8)/2,0))<-1.8,"LE","No signal"))

 

[GraH]

Nice one, roundup and no volatiele construction required.

 

[Peter_SSs]

Less calculation would be to have separate formulas in F8 & F9 then select both those cells and copy down.

20 07 26.xlsm
	A					F
8	NATURALGAS20JULFUT					SE
9	NATURALGAS20AUGFUT					SE
10	NATURALGAS20JULFUT					No signal
11	NATURALGAS20AUGFUT					No signal
12	NATURALGAS20JULFUT					No signal
13	NATURALGAS20AUGFUT					No signal
14	NATURALGAS20JULFUT					LE
15	NATURALGAS20AUGFUT					LE
EXCEL SIGNAL GEN.

Cell Formulas
Range	Formula
F8,F10,F12,F14	F8	=IF(INDEX(ALGO!E:E,24+ROWS(F$8:F8)/2)>1.8,"SE ",IF(INDEX(ALGO!E:E,24+ROWS(F$8:F8)/2)<-1.8,"LE","No signal"))
F9,F11,F13,F15	F9	=F8