determining the largest value in a sequence of text strings

D

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(D6:D13,3),D6:D13)) would return
002, but this leaves out the letter component.

Any ideas?

Thanks.
 
D

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

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
(D6:D13,">"&D6:D13)=0)*
(D6:D13<>"")),D6:D13)

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
 
D

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
(D6:D13,">"&D6:D13)=0)*
(D6:D13<>"")),D6:D13)

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
Click to expand...
 
T

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Wow, great that works. Thanks!
 
D

Dave F

Can someone explain to me how this formula works?

=LOOKUP(2,1/((COUNTIF(D6:D41,">"&D6:D41)=0)*(D6:D41<>"")),D6:D41)

Thanks.

Dave
 
Top