if conditions meet then show a list of columns/cells formula

[vishu]

Hello friends,
Need Help. I have 6 boxes if conditions meet then accordingly box or formula should display in the blank box.

Testing.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2
3		Input									A
4		970									OPEN PRICE BETWEEN H3 & L3
5											WAIT FOR PRICE TO GO BELOW L3 , ONCE ABOVE L3 BUY
6			978.9								BUY		966.38
7			949								STOP LOSS		962.17
8			974.5		A Between H3 & L3						T1		977.24
9											T2		979.97
10	H7	1014.87									T3		982.72
11	H6	1007.09
12	H5	999.02									WAIT FOR PRICE TO GO ABOVE H3, ONCE BELOW H3, SELL
13	H4	990.95									SELL		982.62
14	H3	982.72									STOP LOSS		986.83
15	H2	979.97									T1		971.76
16	H1	977.24									T2		969.03
17											T3		966.28
18
19	L1	971.76									B
20	L2	969.03									OPEN PRICE BETWEEN H3 & H4
21	L3	966.28									WAIT FOR PRIVE ABOVE H4 , ONCE ABOVE H4 BUY
22	L4	958.06									BUY		991.05
23	L5	949.98									STOP LOSS		986.83
24	L6	941.91									T1		996.00
25	L7	934.14									T2		1000.96
26											T3		1005.91
27
28											WAIT FOR PRICE TO GO BELOW H3, ONCE BELOW H3, SELL
29											SELL		982.62
30											STOP LOSS		986.83
31											T1		971.76
32											T2		969.03
33											T3		966.28
34
35											C
36											OPEN PRICE BETWEEN L3 & L4
37											WAIT FOR PRICE TO GO ABOVE L3 , ONCE ABOVE L3 BUY
38											BUY		966.38
39											STOP LOSS		962.17
40											T1		977.24
41											T2		979.97
42											T3		982.72
43
44											WAIT FOR PRICE TO GO BELOW L4, ONCE BELOW L4, SELL
45											SELL		957.96
46											STOP LOSS		962.17
47											T1		953.17
48											T2		948.38
49											T3		943.59
50
51											D
52											OPEN PRICE IS ABOVE H4
53											WAIT FOR PRICE TO GO ABOVE H4, ONCE ABOVE H4 BUY
54											BUY		991.05
55											STOP LOSS		986.83
56											T1		999.02
57											T2		1007.09
58											T3		1014.87
59
60											WAIT FOR PRICE TO GO BELOW H3, ONCE BELOW H3, SELL
61											SELL		982.62
62											STOP LOSS		986.83
63											T1		971.76
64											T2		969.03
65											T3		966.28
66
67											E
68											OPEN PRICE IS BELOW L4
69											WAIT FOR PRICE TO GO ABOVE L3, ONCE ABOVE L3, BUY
70											BUY		966.38
71											STOP LOSS		962.17
72											T1		977.24
73											T2		979.97
74											T3		982.72
75
76											WAIT FOR PRICE TO GO BELOW L4, ONCE BELOW L4, SELL
77											SELL		958.16
78											STOP LOSS		962.17
79											T1		949.98
80											T2		941.91
81											T3		934.14
82
83											F
84
85
86
87
88
89
90
91
92
93
94
95
96
97
1

Cell Formulas
Range	Formula
F8	F8	=IF(AND(C4>((C10*0.2%)+C10)),"Wait for Range", IF(AND(C4>C21,C4<C14),"A Between H3 & L3", IF(AND(C4>C14,C4<C13),"B Between H3 & H4", IF(AND(C4>C22,C4<C21),"C Between L3 & L4", IF(AND(C4>C13),"D Above H4", IF(AND(C4<C22),"E Below L4", IF(AND(C4>((C24*0.2%)+C24)),"Wait for Range")))))))
N6	N6	=C21+0.1
N7	N7	=(C21+C22)/2
N8	N8	=C16
N9	N9	=C15
N10,N15:N17	N10	=C14
C10	C10	=1.35*(D6-D7)+D8
C11	C11	=1.09*(D6-D7)+D8
C12	C12	=0.82*(D6-D7)+D8
C13	C13	=0.55*(D6-D7)+D8
C14	C14	=0.275*(D6-D7)+D8
C15	C15	=0.183*(D6-D7)+D8
C16	C16	=0.0916*(D6-D7)+D8
N13	N13	=C14-0.1
N14	N14	=(C13+C14)/2
C19	C19	=D8-(0.0916*(D6-D7) )
C20	C20	=D8-(0.183*(D6-D7) )
C21	C21	=D8-(0.275*(D6-D7))
C22	C22	=D8-(0.55*(D6-D7))
C23	C23	=D8-(0.82*(D6-D7))
C24	C24	=D8-(1.09*(D6-D7))
C25	C25	=D8-(1.35*(D6-D7))
N22	N22	=C13+0.1
N23	N23	=(C13+C14)/2
N24	N24	=N22+(N22*0.5/100)
N25	N25	=N22+(N22*1/100)
N26	N26	=N22+(N22*1.5/100)
N29	N29	=C14-0.1
N30	N30	=(C13+C14)/2
N31:N33	N31	=C19
N38	N38	=C21+0.1
N39	N39	=(C21+C22)/2
N40	N40	=C16
N41	N41	=C15
N42	N42	=C14
N45	N45	=C22-0.1
N46	N46	=(C21+C22)/2
N47	N47	=N45-(N45*0.5/100)
N48	N48	=N45-(N45*1/100)
N49	N49	=N45-(N45*1.5/100)
N54	N54	=C13+0.1
N55	N55	=(C13+C14)/2
N56,N63:N65	N56	=C12
N57	N57	=C11
N58	N58	=C10
N61	N61	=C14-0.1
N62	N62	=(C13+C14)/2
N70	N70	=C21+0.1
N71	N71	=(C22+C21)/2
N72,N79:N81	N72	=C16
N73	N73	=C15
N74	N74	=C14
N77	N77	=C22+0.1
N78	N78	=(C22+C21)/2

 

[vishu]

hello friends,
I am able to extract data using formulas but still having problems. Unwanted formulas are removed. Please help me.

Testing.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2
3		Input								A					Helper 1	Helper 2	Helper 3
4		900					FALSE		A	OPEN PRICE BETWEEN H3 & L3					1		15
5									A	WAIT FOR PRICE TO GO BELOW L3 , ONCE ABOVE L3 BUY					2		16
6			978.9						A	BUY		966.38			3		31
7			949						A	STOP LOSS		962.17			4		32
8			974.5		OPEN PRICE BELOW L4				A	T1		977.24			5		47
9									A	T2		979.97			6		48
10	H7	1014.87			B				A	T3		982.72			7		63
11	H6	1007.09			OPEN PRICE BETWEEN H3 & H4		0.00		A						8		64
12	H5	999.02			C		0.00		A	WAIT FOR PRICE TO GO ABOVE H3, ONCE BELOW H3, SELL					9		79
13	H4	990.95			OPEN PRICE BETWEEN L3 & L4		0.00		A	SELL		982.62			10		80
14	H3	982.72			D		0.00		A	STOP LOSS		986.83			11
15	H2	979.97			OPEN PRICE IS ABOVE H4		0.00		A	T1		971.76			12
16	H1	977.24							A	T2		969.03			13
17					OPEN PRICE IS BELOW L4				A	T3		966.28			14
18					F		0.00								15	15
19	L1	971.76			WAIT FOR RANGE		0.00			B					16	16
20	L2	969.03							B	OPEN PRICE BETWEEN H3 & H4					17
21	L3	966.28							B	WAIT FOR PRIVE ABOVE H4 , ONCE ABOVE H4 BUY					18
22	L4	958.06							B	BUY		991.05			19
23	L5	949.98							B	STOP LOSS		986.83			20
24	L6	941.91							B	T1		996.00			21
25	L7	934.14							B	T2		1000.96			22
26									B	T3		1005.91			23
27									B						24
28									B	WAIT FOR PRICE TO GO BELOW H3, ONCE BELOW H3, SELL					25
29									B	SELL		982.62			26
30									B	STOP LOSS		986.83			27
31									B	T1		971.76			28
32									B	T2		969.03			29
33									B	T3		966.28			30
34															31	31
35										C					32	32
36									C	OPEN PRICE BETWEEN L3 & L4					33
37									C	WAIT FOR PRICE TO GO ABOVE L3 , ONCE ABOVE L3 BUY					34
38									C	BUY		966.38			35
39									C	STOP LOSS		962.17			36
40									C	T1		977.24			37
41									C	T2		979.97			38
42									C	T3		982.72			39
43									C						40
44									C	WAIT FOR PRICE TO GO BELOW L4, ONCE BELOW L4, SELL					41
45									C	SELL		957.96			42
46									C	STOP LOSS		962.17			43
47									C	T1		953.17			44
48									C	T2		948.38			45
49									C	T3		943.59			46
50															47	47
51										D					48	48
52									D	OPEN PRICE IS ABOVE H4					49
53									D	WAIT FOR PRICE TO GO ABOVE H4, ONCE ABOVE H4 BUY					50
54									D	BUY		991.05			51
55									D	STOP LOSS		986.83			52
56									D	T1		999.02			53
57									D	T2		1007.09			54
58									D	T3		1014.87			55
59									D						56
60									D	WAIT FOR PRICE TO GO BELOW H3, ONCE BELOW H3, SELL					57
61									D	SELL		982.62			58
62									D	STOP LOSS		986.83			59
63									D	T1		971.76			60
64									D	T2		969.03			61
65									D	T3		966.28			62
66															63	63
67										E					64	64
68									E	OPEN PRICE IS BELOW L4					65
69									E	WAIT FOR PRICE TO GO ABOVE L3, ONCE ABOVE L3, BUY					66
70									E	BUY		966.38			67
71									E	STOP LOSS		962.17			68
72									E	T1		977.24			69
73									E	T2		979.97			70
74									E	T3		982.72			71
75									E						72
76									E	WAIT FOR PRICE TO GO BELOW L4, ONCE BELOW L4, SELL					73
77									E	SELL		958.16			74
78									E	STOP LOSS		962.17			75
79									E	T1		949.98			76
80									E	T2		941.91			77
81									E	T3		934.14			78
82															79	79
83										F					80	80
84									F	WAIT FOR RANGE					81
85									F						82
86									F						83
87									F						84
88									F						85
89									F						86
90									F						87
91									F						88
92									F						89
93									F						90
94									F						91
95									F						92
96									F						93
97									F						94
1 (2)

Cell Formulas
Range	Formula
H4	H4	=IF(AND($F$8=K4),"A", IF(AND($F$8=K20),"B", IF(AND($F$8=K36),"C", IF(AND($F$8=K52),"D", IF(AND($F$8=K68),"E", IF(AND($F$8=K84),"F"))))))
P4:P97	P4	=ROWS(K$4:$K4)
Q4:Q97	Q4	=IF(J4=$H$4,P4,"")
R4:R97	R4	=IFERROR(SMALL($Q$4:$Q$97,P4),"")
F8	F8	=IF(AND(C4>((C10*0.5%)+C10)),"WAIT FOR RANGE", IF(AND(C4>C21,C4<C14),"OPEN PRICE BETWEEN H3 & L3", IF(AND(C4>C14,C4<C13),"OPEN PRICE BETWEEN H3 & H4", IF(AND(C4>C22,C4<C21),"OPEN PRICE BETWEEN L3 & L4", IF(AND(C4>C13),"OPEN PRICE ABOVE H4", IF(AND(C4<C22),"OPEN PRICE BELOW L4", IF(C4<(C25-(C25*0.5%)),"WAIT FOR RANGE")))))))
H18:H22,F17:F22,H11:H15,F10:F15	F10	=IFERROR(INDEX($K$5:$O$97,$R4,COLUMNS($F$9:F9)),"")

 

[vishu]

friends, please any solution

 

[vishu]

hello friends,
I am able to extract data using formulas but still having problems. Unwanted formulas are removed. Please help me.

Testing.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2
3		Input								A					Helper 1	Helper 2	Helper 3
4		900					FALSE		A	OPEN PRICE BETWEEN H3 & L3					1		15
5									A	WAIT FOR PRICE TO GO BELOW L3 , ONCE ABOVE L3 BUY					2		16
6			978.9						A	BUY		966.38			3		31
7			949						A	STOP LOSS		962.17			4		32
8			974.5		OPEN PRICE BELOW L4				A	T1		977.24			5		47
9									A	T2		979.97			6		48
10	H7	1014.87			B				A	T3		982.72			7		63
11	H6	1007.09			OPEN PRICE BETWEEN H3 & H4		0.00		A						8		64
12	H5	999.02			C		0.00		A	WAIT FOR PRICE TO GO ABOVE H3, ONCE BELOW H3, SELL					9		79
13	H4	990.95			OPEN PRICE BETWEEN L3 & L4		0.00		A	SELL		982.62			10		80
14	H3	982.72			D		0.00		A	STOP LOSS		986.83			11
15	H2	979.97			OPEN PRICE IS ABOVE H4		0.00		A	T1		971.76			12
16	H1	977.24							A	T2		969.03			13
17					OPEN PRICE IS BELOW L4				A	T3		966.28			14
18					F		0.00								15	15
19	L1	971.76			WAIT FOR RANGE		0.00			B					16	16
20	L2	969.03							B	OPEN PRICE BETWEEN H3 & H4					17
21	L3	966.28							B	WAIT FOR PRIVE ABOVE H4 , ONCE ABOVE H4 BUY					18
22	L4	958.06							B	BUY		991.05			19
23	L5	949.98							B	STOP LOSS		986.83			20
24	L6	941.91							B	T1		996.00			21
25	L7	934.14							B	T2		1000.96			22
26									B	T3		1005.91			23
27									B						24
28									B	WAIT FOR PRICE TO GO BELOW H3, ONCE BELOW H3, SELL					25
29									B	SELL		982.62			26
30									B	STOP LOSS		986.83			27
31									B	T1		971.76			28
32									B	T2		969.03			29
33									B	T3		966.28			30
34															31	31
35										C					32	32
36									C	OPEN PRICE BETWEEN L3 & L4					33
37									C	WAIT FOR PRICE TO GO ABOVE L3 , ONCE ABOVE L3 BUY					34
38									C	BUY		966.38			35
39									C	STOP LOSS		962.17			36
40									C	T1		977.24			37
41									C	T2		979.97			38
42									C	T3		982.72			39
43									C						40
44									C	WAIT FOR PRICE TO GO BELOW L4, ONCE BELOW L4, SELL					41
45									C	SELL		957.96			42
46									C	STOP LOSS		962.17			43
47									C	T1		953.17			44
48									C	T2		948.38			45
49									C	T3		943.59			46
50															47	47
51										D					48	48
52									D	OPEN PRICE IS ABOVE H4					49
53									D	WAIT FOR PRICE TO GO ABOVE H4, ONCE ABOVE H4 BUY					50
54									D	BUY		991.05			51
55									D	STOP LOSS		986.83			52
56									D	T1		999.02			53
57									D	T2		1007.09			54
58									D	T3		1014.87			55
59									D						56
60									D	WAIT FOR PRICE TO GO BELOW H3, ONCE BELOW H3, SELL					57
61									D	SELL		982.62			58
62									D	STOP LOSS		986.83			59
63									D	T1		971.76			60
64									D	T2		969.03			61
65									D	T3		966.28			62
66															63	63
67										E					64	64
68									E	OPEN PRICE IS BELOW L4					65
69									E	WAIT FOR PRICE TO GO ABOVE L3, ONCE ABOVE L3, BUY					66
70									E	BUY		966.38			67
71									E	STOP LOSS		962.17			68
72									E	T1		977.24			69
73									E	T2		979.97			70
74									E	T3		982.72			71
75									E						72
76									E	WAIT FOR PRICE TO GO BELOW L4, ONCE BELOW L4, SELL					73
77									E	SELL		958.16			74
78									E	STOP LOSS		962.17			75
79									E	T1		949.98			76
80									E	T2		941.91			77
81									E	T3		934.14			78
82															79	79
83										F					80	80
84									F	WAIT FOR RANGE					81
85									F						82
86									F						83
87									F						84
88									F						85
89									F						86
90									F						87
91									F						88
92									F						89
93									F						90
94									F						91
95									F						92
96									F						93
97									F						94
1 (2)

Cell Formulas
Range	Formula
H4	H4	=IF(AND($F$8=K4),"A", IF(AND($F$8=K20),"B", IF(AND($F$8=K36),"C", IF(AND($F$8=K52),"D", IF(AND($F$8=K68),"E", IF(AND($F$8=K84),"F"))))))
P4:P97	P4	=ROWS(K$4:$K4)
Q4:Q97	Q4	=IF(J4=$H$4,P4,"")
R4:R97	R4	=IFERROR(SMALL($Q$4:$Q$97,P4),"")
F8	F8	=IF(AND(C4>((C10*0.5%)+C10)),"WAIT FOR RANGE", IF(AND(C4>C21,C4<C14),"OPEN PRICE BETWEEN H3 & L3", IF(AND(C4>C14,C4<C13),"OPEN PRICE BETWEEN H3 & H4", IF(AND(C4>C22,C4<C21),"OPEN PRICE BETWEEN L3 & L4", IF(AND(C4>C13),"OPEN PRICE ABOVE H4", IF(AND(C4<C22),"OPEN PRICE BELOW L4", IF(C4<(C25-(C25*0.5%)),"WAIT FOR RANGE")))))))
H18:H22,F17:F22,H11:H15,F10:F15	F10	=IFERROR(INDEX($K$5:$O$97,$R4,COLUMNS($F$9:F9)),"")

got this answer from another forum
H4
=IF($F$8=K4,"A",IF($F$8=K20,"B",IF($F$8=K36,"C",IF($F$8=K52,"D",IF($F$8=K68,"E","F")))))