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 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)


--

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)


--

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)


--

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)


--

Thanks for the reply JMB.
Is it any easier to get the MAX of these cells:
B7=MAX(D7,G7,I7,K7) witht he same "st xxxx" format ?


"JMB" wrote in message
...
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)


--

Could not think of a way to do it w/o converting each cell. Assuming none of
the cells are empty or blank, one way:

=MAX(--RIGHT(D7,4), --RIGHT(G7,4), --RIGHT(I7,4), --RIGHT(K7,4))

Or if there is no data where the last 4 characters are numeric in the cells
between D7 and K7:
=MAX(IF(ISNUMBER(--RIGHT(D7:K7,4)), --RIGHT(D7:K7,4)))
array entered


Is your project dependent on the cells containing the text "st " (since all
of the data is in this format it seems to me the "st " is more for
presentation and wouldn't be absolutely necessary for your worksheet formulae
to work). Is it an option to use a custom number format of
"st "#

That way, your data is presented as "st 1234", but the cell will actually
contain the number 1234. Then you can dispense w/ having to pull out the
number part and just use the MAX function normally. You could use
Find/Replace to replace
st<space
replace with = <nothing-leave it empty

to get rid of the text "st " in your data and convert it to numbers. You
would have to be careful the Find/Replace doesn't affect other data on the
worksheet you do not intend to convert (perhaps copy the data you want
converted to a sheet by itself-do the Find/Replace and then copy it back to
it's original location).

If you do try it, be sure to backup.



"Corey" wrote:

Thanks for the reply JMB.
Is it any easier to get the MAX of these cells:
B7=MAX(D7,G7,I7,K7) witht he same "st xxxx" format ?


"JMB" wrote in message
...
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)


--

Try:

=MAX((IF(ISERROR(--RIGHT($2:$1000,4)),
-9999,--RIGHT($2:$1000,4))))

I put in -9999 as the error value as you specified positve integers in the
numbers.

This is a CSE (control shift enter) array formula and it picks up blank
cells and non conforming cells (to a certain extent ie it won't pick up AS
123445 and it will give 3445).

You cannot specify the cell were the formula is.

Be very careful if you specify the whole worksheet (ie 2:65536 or even worse
in 12)as it is very slow.......

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"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...

try this array formula. Enter using ctrl+shift+enter
=MAX(IF(LEFT($A$1:$D$21,2)="st",--(RIGHT($A$1:$D$21,LEN($A$1:$D$21)-2))))

--
Don Guillett
SalesAid Software

"Corey" wrote in message ...
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....

=MAX(SUBSTITUTE(A:I,"st ","")+0)

ctrl+shift+enter, not just enter


"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...