How to find maximum value in a range of more than 255 numbers? MAX does not work

[Tille]

I have a table of historical daily data for a few years, so well over a thousand records and I want to find the maximum value in a given column for all records.

But the MAX function apparently allows only up to 255 values in its range, and I can't find any function that will find the maximum value in a column of over 255 values.

Any solution?

 

[kevin9999]

That simply isn't true (see below demo looking at 100K cells in column A).
Could you provide a copy of the sheet in question (you can disguise any sensitive data) using the XL2BB add in as there may be something else going on?

Book1
	A	B
1	1	100000
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MAX(A1:A100000)

 

[Tille]

To be complete:

The maximum 255 arguments is a number I found mentioned in some articles on the MAX function when I was looking for solutions to my problem.

In my own worksheet, the maximum number of values searched for which MAX will return a correct answer is only 123. No idea why.

So for example: MAX(A1:A123) will give me a correct maximum value, but extending the range one single more line to MAX(A1:A124) will generate a N/A.

One last thing, the column of values I am searching contain vlookup functions. Not sure how that factors in...

 

[Tille]

That simply isn't true (see below demo looking at 100K cells in column A).
Could you provide a copy of the sheet in question (you can disguise any sensitive data) using the XL2BB add in as there may be something else going on?

Book1
	A	B
1	1	100000
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MAX(A1:A100000)

You are correct. I just tried MAX on a different column containing just numbers, not function results, and it seems to work fine.

So it seems that the vlookup results are diminishing the range that can be used in MAX.

Very puzzling

 

[kevin9999]

Could you share your worksheet via Dropbox, Google Drive or something similar?

 

[Tille]

You are correct. I just tried MAX on a different column containing just numbers, not function results, and it seems to work fine.

So it seems that the vlookup results are diminishing the range that can be used in MAX.

Very puzzling

That simply isn't true (see below demo looking at 100K cells in column A).
Could you provide a copy of the sheet in question (you can disguise any sensitive data) using the XL2BB add in as there may be something else going on?

Book1
	A	B
1	1	100000
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MAX(A1:A100000)

Sorry, I don't know how to use add-ins, am just a very basic user.

It's ok, I'll just work around the issue and copy paste my vlookup column as values.

Thanks!

 

[Peter_SSs]

So for example: MAX(A1:A123) will give me a correct maximum value, but extending the range one single more line to MAX(A1:A124) will generate a N/A

Welcome to the MrExcel board!
That sounds to me like one or more of the formulas could be returning a #N/A result (though perhaps it may be hidden with conditional formatting?)

In any case, try this on your original data

=AGGREGATE(14,6,A1:A10000,1)