Find large or max with logical values

**ianh** · 06-29-2011, 05:23 AM

I have a set of alpha numeric values in a scale 3c,3b,3c,4c,4b,4a
I want to be able to add a formula in the next column to return the largest value.
I thought either max or large could be used to return a value but neither work on logical values.

I cant convert the values numbers to a number only.

I would use an if statement but this would get very large (there can but multiple columns to check)

anyone have an idea?

**TMS** · 06-29-2011, 05:58 AM

If they are all of that format, one way, maybe:

=DEC2HEX(MAX(HEX2DEC(A2),HEX2DEC(B2),HEX2DEC(C2),HEX2DEC(D2)))

Regards

**ianh** · 06-29-2011, 06:16 AM

Thanks for that but it wont work in this instance as the scale is 4c is lower than 4b is lower than 4a.
so the formula returns 4c not 4a.

**martindwilson** · 06-29-2011, 06:46 AM

=LEFT(MAX(--LEFT(A2:D2)+1/CODE(RIGHT(A2:D2))))&CHAR(1/(MAX(--LEFT(A2:D2)+1/CODE(RIGHT(A2:D2)))-LEFT(MAX(--LEFT(A2:D2)+1/CODE(RIGHT(A2:D2))))))
array entered with contol shift +enter will work for single digit with any letter a-z

**ianh** · 06-29-2011, 10:04 AM

Thanks Martin,

Works a treat, being an array really helps as the number of cells will change over time so i can specify the range rather than a messy nested if.

I've never used the CHAR or CODE before and can see how useful they will be to me for other sheets i'm working on. you've just saved me loads of time in the future.