Return maximum whole number from column of data

[Frog1]

Greetings All!

I have a column of data (column A) in which there are whole numbers (1,2,3...etc) and other numbers that may be negative and contain decimal points. I attach a sample of the data. Can someone help with an excel formula that would return the maximum whole number and NOT to include any negative number or that contains a decimal point and then add '1' to this maximum number and paste it below the data column of interest. eg from the sample data the maximum whole number value is '7' [from row 53] -- therefore I wish to insert the number '8' into cell A61. One may notice that the data column follows a similar pattern: there is a 'blank' cell followed by a whole number then three more 'blank' cells then a varying number of cells that contain negative numbers and/or numbers with decimal points, then a blank cell and the pattern is repeated. NB these are blank cells are not empty but contain a formula. I hope I have managed to explain the question clearly and....oh yes "A Happy New Year"

	A
1
2	1
3
4
5
6	3.26
7	7.806
8
9	2
10
11
12
13	-0.74
14	2.413
15	13.04
16	10.72
17
18	3
19
20
21
22	13.98
23	-0.08
24	6.376
25	16.54
26	11.94
27
28	4
29
30
31
32	10.05
33
34	5
35
36
37
38	24.93
39	9.227
40	5.226
41	-1.7
42
43	6
44
45
46
47	-2.94
48	3.71
49	6.15
50	3.744
51	5.092
52
53	7
54
55
56
57	9.77
58	6.466
59	11.36
60
61

 

[Kaper]

If you want "all in one" formula and have old (2016 or '13) version of Excel my proposition for B1 and coy down is an array * formula:

=IF(INDEX(A$1:A$100,MAX(1,ROW()-MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,""))-1))=MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,"")),MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,""))+1,"")

To have array formula active in older versions of Excel, like yours you shall paste the formula to formula bar or to a cell but not commit it with just Enter but with Ctrl+Shift+Enter combination.
If you look into formula bar after that, you will see the formula surrounded by curly braces. But you can't write them from the keybord. These curly braces have to be result of pressing Ctrl+Shift+Enter keys combination.

 

[hagia_sofia]

Another option:

=AGGREGATE(14,6,(TRUNC(A2:A60,0)=A2:A60)*A2:A60,1)+1

 

[Kaper]

@hagia_sophia : But this will only calculate 8 wherever it is posted, not positioning the result only in row 61

 

[Kaper]

If you use the above AGGREGATE formula (or equivalent to it array committed formula):

=MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,""))+1

and one of these formulas is in E1, you can write in B1 and copy down such standard (just Enter committed) formula:

=IF(INDEX(A$1:A$100,MAX(1,ROW()-E$1))=E$1-1,E$1,"")

See all formulas in action in columns B, C and D of this mini-sheet (C and D refer to E1 and F1 resp.):

Book1
	A	B	C	D	E	F
1					8	8
2	1
3
4
5
6	3,26
7	7,806
8
9	2
10
11
12
13	-0,74
14	2,413
15	13,04
16	10,72
17
18	3
19
20
21
22	13,98
23	-0,08
24	6,376
25	16,54
26	11,94
27
28	4
29
30
31
32	10,05
33
34	5
35
36
37
38	24,93
39	9,227
40	5,226
41	-1,7
42
43	6
44
45
46
47	-2,94
48	3,71
49	6,15
50	3,744
51	5,092
52
53	7
54
55
56
57	9,77
58	6,466
59	11,36
60
61		8	8	8
62
63
Sheet2

Cell Formulas
Range	Formula
B1:B63	B1	=IF(INDEX(A$1:A$100,MAX(1,ROW()-MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,""))-1))=MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,"")),MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,""))+1,"")
C1:C63	C1	=IF(INDEX(A$1:A$100,MAX(1,ROW()-E$1))=E$1-1,E$1,"")
D1:D63	D1	=IF(INDEX(A$1:A$100,MAX(1,ROW()-F$1))=F$1-1,F$1,"")
E1	E1	=MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,""))+1
F1	F1	=AGGREGATE(14,6,(TRUNC(A2:A60,0)=A2:A60)*A2:A60,1)+1
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hagia_sofia]

@hagia_sophia : But this will only calculate 8 wherever it is posted, not positioning the result only in row 61

@Kaper: you are right; my understanding of the question was that there is a set of data and the formula should be right below it but it might be a wrong reading.

 

[Kaper]

As I understand, if there is no data in A18 and below, the result shall be 3, and shall be located in row 12 (3 rows below value 2 in A9)

 

[hagia_sofia]

Ah, I see.

 

[Frog1]

If you want "all in one" formula and have old (2016 or '13) version of Excel my proposition for B1 and coy down is an array * formula:

=IF(INDEX(A$1:A$100,MAX(1,ROW()-MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,""))-1))=MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,"")),MAX(IF(A$1:A$100=INT(A$1:A$100),A$1:A$100,""))+1,"")

To have array formula active in older versions of Excel, like yours you shall paste the formula to formula bar or to a cell but not commit it with just Enter but with Ctrl+Shift+Enter combination.
If you look into formula bar after that, you will see the formula surrounded by curly braces. But you can't write them from the keybord. These curly braces have to be result of pressing Ctrl+Shift+Enter keys combination.

Hi Kaper

Thank you for this response. I will get to trying your idea as soon as the New Year festivities have slowed down. I do appreciate the quick reply -- in fact I am amazed at the responses during this holiday season

Frog1

 

[Kaper]

Celebrating New Year's Eve and New Year not only by partying but also by pursuing a hobby is a good idea after all, isn't it?
BTW, yesterday afternoon and today morning I was biking too, so continuity between 2024 and 2025 is fully maintained <3