Very Complicated Stock Formula's that Ignore 4th, 5th, 6th, etc Day Trends and Isolate 3 Day Trends

[rebel123]

Here is an example of a company called Novavax (Ticker NVAX)
There are 2 scenarios, that I am seeking solutions. This is only part of their pricing history, so
if I have several more rows, the formula would need to work on that as well, please.

Both scenarios detect when a stocks price goes down 3 days in a row (meaning- Close to Close).
HOWEVER, the formula must ignore if a stock goes down more than 3 in a row.
For example, if a stock goes down down down down down, then it would only register as 1 result.


SCENARIO 1

Date	Open	High	Low	Close	Difference	Find The Sequence	Total Answers	Gain Percentage	Day 4 Close Minus 4 Day Open	Average of the Results
Jul 18, 2023	$8.13	$8.68	$7.94	$8.11	-$0.05		(Need an Equation. Answer is 2)	(Need an Equation. Answer is 0% Since Both Were Negative)		-$0.365
Jul 17, 2023	$8.70	$8.74	$8.14	$8.16	-$0.52				-$0.54
Jul 14, 2023	$9.21	$9.23	$8.51	$8.68	-$0.56	<- ONE RESULT
Jul 13, 2023	$9.38	$9.47	$9.16	$9.24	-$0.10
Jul 12, 2023	$9.88	$9.92	$9.28	$9.34	-$0.28
Jul 11, 2023	$9.11	$10.18	$8.85	$9.62	$0.26
Jul 10, 2023	$7.35	$9.55	$7.30	$9.36	$2.13
Jul 07, 2023	$7.05	$7.34	$7.05	$7.23	$0.17
Jul 06, 2023	$7.25	$7.34	$6.90	$7.06	-$0.30				-$0.19
Jul 05, 2023	$7.38	$7.46	$7.21	$7.36	-$0.02	<- ONE RESULT
Jul 03, 2023	$7.48	$7.59	$7.22	$7.38	-$0.05
Jun 30, 23	$7.62	$7.76	$7.39	$7.43	-$0.16
Jun 29, 23	$7.53	$7.83	$7.45	$7.59	$0.06
Jun 28, 23	$7.17	$7.59	$7.03	$7.53

So that you have a visual, I would need the calculations where the YELLOW HIGHLIGHTED cells are.
Where it says <<- ONE RESULT You could rename it to whatever you wish like MATCH or YES.

SCENARIO 2

Date	Open	High	Low	Close	Difference	Find The Sequence	Total Answers	Gain Percentage	Day 4 Open - 3 Day Close	Average of the Results
Jul 18, 2023	$8.13	$8.68	$7.94	$8.11	-$0.05		(Need an Equation. Answer is 2)	(Need an Equation. Answer is 50% Since 1 Positive & 1 Negative)		-$0.045
Jul 17, 2023	$8.70	$8.74	$8.14	$8.16	-$0.52				$0.02
Jul 14, 2023	$9.21	$9.23	$8.51	$8.68	-$0.56	<- ONE RESULT
Jul 13, 2023	$9.38	$9.47	$9.16	$9.24	-$0.10
Jul 12, 2023	$9.88	$9.92	$9.28	$9.34	-$0.28
Jul 11, 2023	$9.11	$10.18	$8.85	$9.62	$0.26
Jul 10, 2023	$7.35	$9.55	$7.30	$9.36	$2.13
Jul 07, 2023	$7.05	$7.34	$7.05	$7.23	$0.17
Jul 06, 2023	$7.25	$7.34	$6.90	$7.06	-$0.30				-$0.11
Jul 05, 2023	$7.38	$7.46	$7.21	$7.36	-$0.02	<- ONE RESULT
Jul 03, 2023	$7.48	$7.59	$7.22	$7.38	-$0.05
Jun 30, 2023	$7.62	$7.76	$7.39	$7.43	-$0.16
Jun 29, 2023	$7.53	$7.83	$7.45	$7.59	$0.06
Jun 28, 2023	$7.17	$7.59	$7.03	$7.53

So that you have a visual, I would need the calculations where the YELLOW HIGHLIGHTED cells are.
Where it says <<- ONE RESULT You could rename it to whatever you wish like MATCH or YES.

 

[Anonymous1378]

Try these? There's nothing overly complicated about them though?
Scenario 1

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Open	High	Low	Close	Difference	Find The Sequence	Total Answers	Gain Percentage	Day 4 Close Minus 4 Day Open	Average of the Results
2	Jul 18, 2023	$8.13	$8.68	$7.94	$8.11	-$0.05		2	0%		-$0.37
3	Jul 17, 2023	$8.70	$8.74	$8.14	$8.16	-$0.52				-$0.54
4	Jul 14, 2023	$9.21	$9.23	$8.51	$8.68	-$0.56	Match
5	Jul 13, 2023	$9.38	$9.47	$9.16	$9.24	-$0.10
6	Jul 12, 2023	$9.88	$9.92	$9.28	$9.34	-$0.28
7	Jul 11, 2023	$9.11	$10.18	$8.85	$9.62	$0.26
8	Jul 10, 2023	$7.35	$9.55	$7.30	$9.36	$2.13
9	Jul 07, 2023	$7.05	$7.34	$7.05	$7.23	$0.17
10	Jul 06, 2023	$7.25	$7.34	$6.90	$7.06	-$0.30				-$0.19
11	Jul 05, 2023	$7.38	$7.46	$7.21	$7.36	-$0.02	Match
12	Jul 03, 2023	$7.48	$7.59	$7.22	$7.38	-$0.05
13	Jun 30, 23	$7.62	$7.76	$7.39	$7.43	-$0.16
14	Jun 29, 23	$7.53	$7.83	$7.45	$7.59	$0.06
15	Jun 28, 23	$7.17	$7.59	$7.03	$7.53
Sheet3

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(G2:G15,"Match")
I2	I2	=COUNTIF(J2:J15,">0")/COUNT(J2:J15)
K2	K2	=AVERAGE(J2:J15)
G2:G15	G2	=IF(AND(F2:F4<0,F5>0),"Match","")
J2:J15	J2	=IF(G3="Match",E2-B2,"")

Scenario 2

Book1
	E	F	G	H	I	J	K
1	Close	Difference	Find The Sequence	Total Answers	Gain Percentage	Day 4 Open - 3 Day Close	Average of the Results
2	$8.11	-$0.05		2	50%		-$0.05
3	$8.16	-$0.52				$0.02
4	$8.68	-$0.56	Match
5	$9.24	-$0.10
6	$9.34	-$0.28
7	$9.62	$0.26
8	$9.36	$2.13
9	$7.23	$0.17
10	$7.06	-$0.30				-$0.11
11	$7.36	-$0.02	Match
12	$7.38	-$0.05
13	$7.43	-$0.16
14	$7.59	$0.06
15	$7.53
Sheet4

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(G2:G15,"Match")
I2	I2	=COUNTIF(J2:J15,">0")/COUNT(J2:J15)
K2	K2	=AVERAGE(J2:J15)
G2:G15	G2	=IF(AND(F2:F4<0,F5>0),"Match","")
J2:J15	J2	=IF(G3="Match",B2-E3,"")

 

[rebel123]

Try these? There's nothing overly complicated about them though?
Scenario 1

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Open	High	Low	Close	Difference	Find The Sequence	Total Answers	Gain Percentage	Day 4 Close Minus 4 Day Open	Average of the Results
2	Jul 18, 2023	$8.13	$8.68	$7.94	$8.11	-$0.05		2	0%		-$0.37
3	Jul 17, 2023	$8.70	$8.74	$8.14	$8.16	-$0.52				-$0.54
4	Jul 14, 2023	$9.21	$9.23	$8.51	$8.68	-$0.56	Match
5	Jul 13, 2023	$9.38	$9.47	$9.16	$9.24	-$0.10
6	Jul 12, 2023	$9.88	$9.92	$9.28	$9.34	-$0.28
7	Jul 11, 2023	$9.11	$10.18	$8.85	$9.62	$0.26
8	Jul 10, 2023	$7.35	$9.55	$7.30	$9.36	$2.13
9	Jul 07, 2023	$7.05	$7.34	$7.05	$7.23	$0.17
10	Jul 06, 2023	$7.25	$7.34	$6.90	$7.06	-$0.30				-$0.19
11	Jul 05, 2023	$7.38	$7.46	$7.21	$7.36	-$0.02	Match
12	Jul 03, 2023	$7.48	$7.59	$7.22	$7.38	-$0.05
13	Jun 30, 23	$7.62	$7.76	$7.39	$7.43	-$0.16
14	Jun 29, 23	$7.53	$7.83	$7.45	$7.59	$0.06
15	Jun 28, 23	$7.17	$7.59	$7.03	$7.53
Sheet3

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(G2:G15,"Match")
I2	I2	=COUNTIF(J2:J15,">0")/COUNT(J2:J15)
K2	K2	=AVERAGE(J2:J15)
G2:G15	G2	=IF(AND(F2:F4<0,F5>0),"Match","")
J2:J15	J2	=IF(G3="Match",E2-B2,"")

Scenario 2

Book1
	E	F	G	H	I	J	K
1	Close	Difference	Find The Sequence	Total Answers	Gain Percentage	Day 4 Open - 3 Day Close	Average of the Results
2	$8.11	-$0.05		2	50%		-$0.05
3	$8.16	-$0.52				$0.02
4	$8.68	-$0.56	Match
5	$9.24	-$0.10
6	$9.34	-$0.28
7	$9.62	$0.26
8	$9.36	$2.13
9	$7.23	$0.17
10	$7.06	-$0.30				-$0.11
11	$7.36	-$0.02	Match
12	$7.38	-$0.05
13	$7.43	-$0.16
14	$7.59	$0.06
15	$7.53
Sheet4

Cell Formulas
Range	Formula
H2	H2	=COUNTIF(G2:G15,"Match")
I2	I2	=COUNTIF(J2:J15,">0")/COUNT(J2:J15)
K2	K2	=AVERAGE(J2:J15)
G2:G15	G2	=IF(AND(F2:F4<0,F5>0),"Match","")
J2:J15	J2	=IF(G3="Match",B2-E3,"")

Thanks I will test this later today!

 

[rebel123]

Thank you so much. It works perfect.