MAX Function not returning correct value

[Phil]

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?

 

[Rae Drysdale]

I would have thought it would be to do with the formatting as text. Format as
Number and run the formula again. Also I would retype the figure 3150 again
just in case!

 

[Phil]

Rae,

Nope. Neither one of your suggestions panned out. Very strange.

 

[Elkar]

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]

Elkar,

Turns out the 3145 was there. But get this. BEFORE, I found it (the 3145),
I thought I would just "create" a new 3145 entry, which I did. Then I did a
search (don't ask me why I didn't see it before - I just don't really know),
and found TWO 3145 entries; the original one that was there ALL of the time,
and the "new" one that I just put it. Realizing this, I deleted the "new"
one, leaving the original one in.

Then, I ran MAX again, and now it returns 3144! NOT 3145!

BTW, I DID check to see if there WERE any leading apostrophes (which I was
pretty sure there weren't).

Then I had an idea. Why not insert a new column next to A, then "recreate"
the index (that's what it really is meant to be) using fill, w/ series, which
I did. Then I ran the MAX function, and it works now. The last value is NOW
3150!

Oh well. Sorry to waste all you people's busy time!

Have a good weekend!

Phil.