Sorry - I now see where you stated the data will always be in the format "st
####". If none of the cells are empty - the fix to the error you're seeing
may just be to not use entire columns (I am assuming you are not using
XL2007).

=MAX(--RIGHT(A1:I100,4))


"JMB" wrote:

Array formulae cannot use entire columns (for versions prior to XL2007).
Also, I get errors if any of the cells in the target range are empty or
contain data where the last 4 characters are non-numeric. Also, the formula
cannot go into cell B1 since that cell is part of the formula's argument
(creating a circular reference).

Be aware that any data that ends w/4 numbers will be included in this
formula, not just data that begins w/ "st ".

This seemed to work okay on my machine, change range as needed.
=MAX(IF(ISNUMBER(--RIGHT(A1:I100,4)),--RIGHT(A1:I100,4)))


"Corey" wrote:

I get a #NUM! Value for some reason with all options here ?




"RaceEend" (rot13) wrote in message
...
Corey wrote:

I need to find the Max Value in a Worksheet,
But the Value will always be in a "st 1234" format.
That is:
"st"+ Space then 4 digits.
I want to find the MAX Numerical value in the entire sheet, and
place this value in B1.
How can I do this ?

Something like :
B1= MAX(sheet!A:I(Right(4)) ???

Corey....

Corey,

B1 = MAX(--RIGHT(Sheet!A:I,4))
Entered as a matrix formula (= CTRL + SHIFT + ENTER)


--