determining the largest value in a sequence of text strings

[Dave F]

Given the following sequence:

001A
001B
001C
001D
002A
002B
002C
002D

what formula can return the greatest value, assuming 002A < 002B <
002C < 002D?

The array formula ="00"&(MAXA(--LEFT(D613,3),D613)) would return
002, but this leaves out the letter component.

Any ideas?

Thanks.

 

[T. Valko]

Try this:

=LOOKUP(2,1/((COUNTIF(D613,">"&D613)=0)*(D613<>"")),D613)

 

[Dave F]

Hi, thanks for the response.

When I copy and paste that formula into Excel I get an error message
saying that the formula contains an error-but I'm not sure what the
error is. What is this formula attempting to do?

Dave

 

[T. Valko]

I see you're posting through Google Groups.

Google Groups is notorious for inserting html (?) formatting characters in
formulas.

The formula does exactly what you wanted, it will return the "largest"
*TEXT* entry from the range.

Here's the formula broken into chunks.

=LOOKUP(2,1/((COUNTIF
(D613,">"&D613)=0)*
(D613<>"")),D613)

There are no dashes "-" in the formula.


--
Biff
Microsoft Excel MVP


Hi, thanks for the response.

When I copy and paste that formula into Excel I get an error message
saying that the formula contains an error-but I'm not sure what the
error is. What is this formula attempting to do?

Dave

 

[Dave F]

Wow, great that works. Thanks!

I see you're posting through Google Groups.

Google Groups is notorious for inserting html (?) formatting characters in
formulas.

The formula does exactly what you wanted, it will return the "largest"
*TEXT* entry from the range.

Here's the formula broken into chunks.

=LOOKUP(2,1/((COUNTIF
(D613,">"&D613)=0)*
(D613<>"")),D613)

There are no dashes "-" in the formula.

--
Biff
Microsoft Excel MVP


Hi, thanks for the response.

When I copy and paste that formula into Excel I get an error message
saying that the formula contains an error-but I'm not sure what the
error is.  What is this formula attempting to do?

Dave

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Wow, great that works. Thanks!

 

[Dave F]

Can someone explain to me how this formula works?

=LOOKUP(2,1/((COUNTIF(D641,">"&D641)=0)*(D641<>"")),D641)

Thanks.

Dave