Find Max Value in WorkSheet

  • Thread starter Thread starter Corey
  • Start date Start date
C

Corey

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 said:
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....
Click to expand...

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.......
 
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
(e-mail address removed)
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....
 
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))
 
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 ?
 
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.
 
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....
 
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
 
Back
Top