finding largest value from list of strings

[Daron]

I have a range that is populated from a db using MS-QUERY. All fields
are formatted from the source as strings. Some columns contain numeric
values.

How do I find that largest value in a column? If the fields where
values, I could use MAX() or LARGE(), but with the fields being
strings, these don't work.

Thanks

- Daron

 

[Rick Rothstein]

Try this array-entered** formula (change the ranges to suit your needs)...

=MAX(IF(ISNUMBER(--A1:A100),--A1:A100,""))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself

 

[Bernd P]

Hello Daron,

Rick showed you how to retrieve the highest numerical value.

If you were looking for the highest alphanumerical (string) value, I
suggest to apply an approach similar to my worksheet function sort:
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

 

[Daron]

Small problem. This range is updated frequently, and can have any
where from a few rows to over 10K rows. Teh source is a parameter
driven DB.

Would the array grow/shrink to match the new size?

 

[Rick Rothstein]

Adjust the range to cover the maximum possible number of rows you ever
expect; for example...

=MAX(IF(ISNUMBER(--A1:A20000),--A1:A20000,""))

--
Rick (MVP - Excel)


Small problem. This range is updated frequently, and can have any
where from a few rows to over 10K rows. Teh source is a parameter
driven DB.

Would the array grow/shrink to match the new size?

 

[Daron]

Sorry, This is not working.

Here is a sample of my data:

"LocCode","StudentID"
"034520","429500070"
"034520","429500087"
"034520","429500499"
"034520","429500071"
"034520","429500072"
"034520","429500073"
"034520","429500340"
"034520","429500342"
"034520","429500088"
"034520","429500353"
"034520","429500346"
"034520","429500344"
"034520","429500074"
"034520","429500359"
"034520","429500075"
"034520","429500372"

This data will not be sorted by the StudentID, and I need to put in a
cell what the highest value is for the StudentID column.

 

[Domenic]

If the values are enclosed within quotes, try...

=MAX(IF(A2:A100<>"",SUBSTITUTE(A2:A100,"""","")+0))

....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

http://www.xl-central.com

 

[Rick Rothstein]

Describe "not working". Are you getting an error (if so, which one)? Is the
formula always returning a blank? Is the formula returning the wrong number?

To be sure, you did change the ranges to reflect the StudentID column,
right? Exactly what is in the cells of your StudentID column... that is, in
what way are those values strings (cell formatted as Text, entry made with
an apostrophe in front, entries surrounded by quote marks, something else)?

--
Rick (MVP - Excel)


Sorry, This is not working.

Here is a sample of my data:

"LocCode","StudentID"
"034520","429500070"
"034520","429500087"
"034520","429500499"
"034520","429500071"
"034520","429500072"
"034520","429500073"
"034520","429500340"
"034520","429500342"
"034520","429500088"
"034520","429500353"
"034520","429500346"
"034520","429500344"
"034520","429500074"
"034520","429500359"
"034520","429500075"
"034520","429500372"

This data will not be sorted by the StudentID, and I need to put in a
cell what the highest value is for the StudentID column.

 

[Daron]

**Commit formula using , not just Enter by itself

You know, wonderful things happen when you read ALL the
instructions...

I used the name of the range to eliminate the need to know the actual
cells, and then did the "Ctrl+Shift+Enter", and it works perfectly.

Would you mind explaining why the array formula works? I am not
familiar with these at all, and any help would be greatly appreciated.

 

[Rick Rothstein]

This link may help you...

http://www.cpearson.com/excel/ArrayFormulas.aspx

--
Rick (MVP - Excel)

**Commit formula using , not just Enter by itself

You know, wonderful things happen when you read ALL the
instructions...

I used the name of the range to eliminate the need to know the actual
cells, and then did the "Ctrl+Shift+Enter", and it works perfectly.

Would you mind explaining why the array formula works? I am not
familiar with these at all, and any help would be greatly appreciated.

 

[Daron]

Thank you!

This link may help you...

http://www.cpearson.com/excel/ArrayFormulas.aspx

--
Rick (MVP - Excel)




You know, wonderful things happen when you read ALL the
instructions...

I used the name of the range to eliminate the need to know the actual
cells, and then did the "Ctrl+Shift+Enter", and it works perfectly.

Would you mind explaining why the array formula works? I am not
familiar with these at all, and any help would be greatly appreciated.