Find Max Value in WorkSheet
 

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

Find Max Value in WorkSheet
 

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)


--

Find Max Value in WorkSheet
 

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

Find Max Value in WorkSheet
 

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

Find Max Value in WorkSheet
 

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

Find Max Value in WorkSheet
 

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)


--

Find Max Value in WorkSheet
 

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)


--

Find Max Value in WorkSheet
 

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)


--

Find Max Value in WorkSheet
 

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)


--

Find Max Value in WorkSheet
 

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)


--

Find Max Value in WorkSheet
 

JMB,
I sem to be abl;e to use the :
=MAX(--RIGHT(D7,4), --RIGHT(G7,4), --RIGHT(I7,4), --RIGHT(K7,4))

with some success.

Is it possible to have the cell ("B7") entered as that formula complete with
Shift+ALT+Enter ?
As i am placing the other St values there froma userform.
I change the values to be (0) zero if no value is needed, to get the formula
to work.

But need to allow this formula to be input into B7 when the userform is
unloaded.

Corey....

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


--

Find Max Value in WorkSheet
 

Got it with:
Range("B7").Select
Selection.FormulaArray = _
"=MAX(--RIGHT(RC[3],4), --RIGHT(RC[5],4), --RIGHT(RC[7],4), --RIGHT(RC[9],4))"

Thanks
"Corey" wrote in message
...
JMB,
I sem to be abl;e to use the :
=MAX(--RIGHT(D7,4), --RIGHT(G7,4), --RIGHT(I7,4), --RIGHT(K7,4))

with some success.

Is it possible to have the cell ("B7") entered as that formula complete
with Shift+ALT+Enter ?
As i am placing the other St values there froma userform.
I change the values to be (0) zero if no value is needed, to get the
formula to work.

But need to allow this formula to be input into B7 when the userform is
unloaded.

Corey....

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


--