maximum text from range

[georgegone]

I am trying to get the maximum from a range of text cells. The data in the cells consist of

00001
00003
00014
00028
00007
etc.


the formula I am using is:
=MAX(VALUE(Interface!E2:E20))

the answer that is returned is 15 and it should be 28. Any ideas on how I can fix this?

 

[barry houdini]

Your formula is an array formula. You need to confirm with CTRL+SHIFT+ENTER......to do that select cell with formula, press F2 key then hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear around the formula in the formula bar

 

[Diablo II]

=max(e2:e20)

 

[georgegone]

It comes back with 19????

 

[georgegone]

=max(e2:e20)


comes back with 0

 

[barry houdini]

If all your data is text formatted then MAX will give 0.....

You say you got 19, was that using my formula "array entered" as instructed? You don't even have a 00019 in your posted data, is there one in there somewhere?

 

[Aladin Akyurek]

=max(e2:e20)


comes back with 0

Control+shift+enter, not just enter:

=MAX(Interface!E2:E20+0)

 

[georgegone]

Barry, My array (sequence) goes from 00001 to 00028 (batch detail). These numbers must be in this format as I am passing them to a MAIN frame, but I must calculate the highest number in the array to put into another part of the file (batch header) and I am trying to create this record. As lines can be deleted even in the middle of the array, I must find the hightest number in this array.

Aladin,

It still comes out as zero, try it.

 

[Aladin Akyurek]

Barry, My array (sequence) goes from 00001 to 00028 (batch detail). These numbers must be in this format as I am passing them to a MAIN frame, but I must calculate the highest number in the array to put into another part of the file (batch header) and I am trying to create this record. As lines can be deleted even in the middle of the array, I must find the hightest number in this array.

Aladin,

It still comes out as zero, try it.

I get 28 with that sample. You need to apply control+shift+enter, not just enter.

 

[georgegone]

thank you,

Had the Cntrol shift enter,but the 28 record was deleted from the array.

It is working. Thank You!