Count Rows

[TexEth]

I need your help with VB code.

Data:
Columns: A to Z
Rows: 1 to 30

Task:
1). Count: Number of rows between the minimum value and the last row. Do for each column.
2). Match (find): The FIRST value AFTER the minimum value, which is >= to the initial value in the column (e.g., A2, B2, etc.).
2). Count: Number of rows between the minimum value and the first value, which is >= to the first value in the column (e.g., A2, B2, etc.)

Return:
Place the results in the bottom three empty rows.

Thank you.

 

[awoohaw]

You can get people to help you better if you paste sample data. The xl2bb add allows you to post a mini workbook of your exact work. XL2BB - Excel Range to BBCode

 

[awoohaw]

does this need to be done via VBA?

 

[awoohaw]

I did it with array formulas and lets.

Mr Excel Questions.xlsx
	A	B	C	D	E	F
2
3		1219	391	380	432	383
4		717	1155	1402	313	1366
5		1266	845	545	366	1324
6		1272	787	1015	532	486
7		1319	1498	363	520	1066
8		1117	944	1078	1021	1362
9		921	754	879	1018	1304
10		671	873	300	1498	704
11		940	912	504	1479	1494
12		353	1243	634	1340	612
13		1475	1354	683	1041	357
14		509	930	708	645	484
15		1153	510	1153	1292	1208
16		1154	372	921	525	390
17		846	921	1178	757	1184
18		461	1294	1161	1339	887
19		1419	1335	965	1244	731
20		1068	953	665	1208	688
21		911	1460	665	394	506
22		800	463	1489	898	787
23		315	359	1456	1155	833
24		319	1301	1209	485	995
25		1085	1175	813	388	1159
26		900	564	1031	520	309
27		1304	1255	414	1111	1463
28		474	1434	710	617	870
29		1250	1292	1354	835	1077
30		680	956	453	436	996
31		1245	1375	1096	445	1001
32		824	562	1196	1419	1325
33
34	Min Value in Column	315
35	Rows Col Min To End	10
36	Next Greater Value After Min Value	1245
37	Rows Min Value to the 2nd Greatest Value In The Column From Top	18
38
39
CountRows

Cell Formulas
Range	Formula
B34	B34	=LET(c, B3:B32, cmin, MIN(B3:B32),cmin)
B35	B35	=LET( c,B3:B32,cMin,MIN(c), cMinToEnd,B32:INDEX(B3:B32,MATCH(cMin,B3:B32,0)), CountRowsMinToEnd, COUNT(cMinToEnd),CountRowsMinToEnd)
B36	B36	=LET( init,B3,c,B3:B32,cMin,MIN(c), rMinPlus1ToEnd,B32:INDEX(B3:B32,1+MATCH(cMin,B3:B32,0)), INDEX(rMinPlus1ToEnd, XMATCH(0.0001, (rMinPlus1ToEnd)*(--(rMinPlus1ToEnd>=init)),1,-1)))
B37	B37	=LET( init,B3,c,B3:B32,cMin,MIN(c), rMinTo2ndFrTop,B4:INDEX(B3:B32,MATCH(cMin,B3:B32,0)-1), locNextLargestAfter1st,XMATCH(0.000001,(rMinTo2ndFrTop)*(--(rMinTo2ndFrTop>=init)),1), COUNT(INDEX(rMinTo2ndFrTop,locNextLargestAfter1st):INDEX(B3:B32,MATCH(cMin,B3:B32,0)-1)) )

 

[TexEth]

The counting of the two rows formula did not work for me. The other two formula codes are working. Please see the sample data.

 

[awoohaw]

Can you give me the examples of what you should see using the data in my worksheet snippet? In the first column?

 

[awoohaw]

I am confused with your requirements statements and with what you have in the initial post.
Instead of this:
1). Count: Number of rows between the minimum value and the last row. Do for each column.
do you mean this:
1). Count: Number of rows between the minimum value and the FIRST row. Do for each column.

and instead of this:
2). Count: Number of rows between the minimum value and the first value, which is >= to the first value in the column (e.g., A2, B2, etc.)
you mean this:
2). Count: Number of rows between the minimum value and the first value, which is >= to the MINIMUM value in the column and >= the first value in the column and AFTER the minimum value

 

[TexEth]

1) Count the rows between the second row (A2) and the minimum value in column A.
I start counting with the 2nd row because the first row is my header row!

2). Count the rows between the minimum value and the next (following) value that is equal to or greater than the initial value (i.e., A2).
The >= A2 value I am interested in should come AFTER the minimum value.
[Here, I am interested to know how long it takes me to rebound to the initial value after hitting rock bottom.]
[Rows represent time.]

Did you see the sample data I posted?

Thank you for your time!

 

[awoohaw]

How does this look? There are discrepancies in how you count the Top To Min, the first column you include either the top or minimum cell. The rest seems okay. I have not read all of your last message as I post this, I hope I have not missed something.

Mr Excel Questions.xlsx
	A	B	C	D	E
1	Year	Dallas	Austin	Houston
2	2000	400	700	1000
3	2001	425	710	1020
4	2002	450	720	1040
5	2003	475	730	1060
6	2004	500	740	1080
7	2005	525	750	1100
8	2006	550	760	1120
9	2007	575	425	1140
10	2008	300	460	1160
11	2009	350	495	1180
12	2010	400	530	1200
13	2011	450	565	1220
14	2012	500	600	1240
15	2013	550	635	1260
16	2014	600	670	800
17	2015	650	705	875
18	2016	700	740	950
19	2017	750	775	1025
20
21		300	425	800	Minimum Value
22		9	7	14	Time Between Initial Value and Minimum Value
23		400	705	1025	First Value after mininum which is >= initial value
24		2	8	3	Number of years between minimum value and recovery value
25
26		300	425	800
27		9	8	15
28		400	705	1025
29		2	8	3
CountRows OP Data

Cell Formulas
Range	Formula
A2:A18	A2	=SEQUENCE(17,1,2000,1)
B26:D26	B26	=LET(c,B$2:B$19,cmin,MIN(c ),cmin)
B27:D27	B27	=LET( c,B$2:B$19, t,B$2, b,B$19, cmin,MIN(c), MinCellRef, INDEX(c,MATCH(cmin,c,0),1), TopToMin,t:MinCellRef, COUNT(TopToMin) )
B28:D28	B28	=LET( c,B$2:B$19, t,B$2, b,B$19, cmin,MIN(c), MinCellRef, INDEX(c,MATCH(cmin,c,0),1), MinToBot,b:INDEX(c,MATCH(cmin,c,0),1), NextLrgHigherThanInit, INDEX(MinToBot,XMATCH(0.00001,MinToBot*(--(MinToBot>=t)),1,1),1), NextLrgHigherThanInit )
B29:D29	B29	=LET( c,B$2:B$19, t,B$2, b,B$19, cmin,MIN(c), MinCellRef, INDEX(c,MATCH(cmin,c,0),1), MinToBot,b:INDEX(c,MATCH(cmin,c,0),1), NextLrgHigherThanInit, INDEX(MinToBot,XMATCH(0.00001,MinToBot*(--(MinToBot>=t)),1,1),1), COUNT(MinCellRef:NextLrgHigherThanInit)-1 )
Dynamic array formulas.

 

[TexEth]

=LET( c,B$2:B$19, t,B$2, b,B$19, cmin,MIN(c), MinCellRef, INDEX(c,MATCH(cmin,c,0),1), MinToBot,b:INDEX(c,MATCH(cmin,c,0),1), NextLrgHigherThanInit, INDEX(MinToBot,XMATCH(0.00001,MinToBot*(--(MinToBot>=t)),1,1),1), COUNT(MinCellRef:NextLrgHigherThanInit)-1 )