Max or Large Function

[lavalleemrl]

I likely need some array function!

In a Column of numbers - several hundred, I want to find the largest
RIGHT 3 digits in the column - in this case 299, and return that value
in 1 cell only. xl 2000.

101-03-223
102-04-277
108-04-299

Thanks very much.
Mike

 

[Jerry W. Lewis]

101-03-223 is not a number, it is a text string of digits. If A1:A300
contain 300 such strings, then the following array formula will give you
what you want

=MAX(VALUE(RIGHT(A1:A300,3)))

Array formulas must be array entered (Ctrl-Shift-Enter) to work properly.

If you want to reference A1:A300, but there are fewer than 300 such
strings in A1:A300 (empty cells etc.), then you will need to ignore
errors with

=MAX(IF(ISNUMBER(VALUE(RIGHT(A1:A300,3))),VALUE(RIGHT(A1:A300,3))))

also array entered.

Jerry

 

[Mike LaVallee]

Thanks Jerry. That worked - sort of.

I have a dynamic named range =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
called "job_number" and when I substitute the dynamic range name for the
cell references, I vet the Value eror.
If the name is not dynamic, it works.

Can anyone help further?

Kind Regards, Mike

 

[Jonathan Rynd]

I have a dynamic named range
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) called "job_number"
and when I substitute the dynamic range name for the cell references,
I vet the Value eror. If the name is not dynamic, it works.

Could you post the exact formula that gives you the #VALUE error? I'd like
to try it out.

 

[Harlan Grove]

...
...

If you want to reference A1:A300, but there are fewer than 300 such
strings in A1:A300 (empty cells etc.), then you will need to ignore
errors with

=MAX(IF(ISNUMBER(VALUE(RIGHT(A1:A300,3))),VALUE(RIGHT(A1:A300,3))))

also array entered.

If blank cells are all that needs to be avoided, cheat.

=MAX(--("0"&RIGHT(A1:A300,3)))