That is a strange one. MAX should not return a value that doesn't exist.
Here's a test you can try. Do an Edit--Find for the value 3145 and see if
anything shows up.

When you say all of your numbers are formatted as text, did you do this by
changing the cell format, or were the numbers entered with an apostrophe?
The apostrophe forces Excel to treat the cell contents as text regarless of
cell formatting. Thus, the MAX function would ignore these values. This
also happens sometimes when data is imported from other applications. The
apostrophe would only be visible in the formula bar, not the cell.

HTH,
Elkar




"Phil" wrote:

Hello,

I have a column (col A) with numbers (formatted as text) which are normally
in sequential series order from 1 to 3150 (but growing every day).
Sometimes, I have to sort the spreadsheet based on an another columns
criteria, and consequently the order from the first column A is not in
sequence anymore. When I run the =MAX(A2:A3151), it returns 3145, which as I
have just discovered, is missing in the series. Nonetheless, it should have
identified 3150 as the highest number, right?

Or does the fact that the 3145 value is missing from the selection set have
to do anything?

I then copied to column over, changed the format to General, and the MAX
returned the same result, 3145, no difference.

Just to see if there was a problem with Excel, I then ran the MAX function
on a new blank spreadsheet with some test values, and it worked. When that
worked, I inserted a new column (B) in the spreadsheet that is giving
trouble, and inserted some random values, ran the MAX function, and it works.
And they are not in sequence, nor are they contigous.

Any idea why this is happening?