finding MIN and MAX

[rcahayag1925]

how do i find the MIN and MAX if it contains text variable

for example my data, looks like these:

BA 12549
BA 12550
BA 12551
BA 12552
BA 12553
BA 12554

i want only the numbers for my min and max

MIN, in this case is 12549
MAX, is 12554

thank you

[samba_ravi]

is the data in single column or in separate columns

[rcahayag1925]

nflsales, in a single column

[quekbc]

If there are always 5 numbers (i.e. BA #####) then...

Please Login or Register  to view this content.

[samba_ravi]

Will you pls attach a sample excel file

[:) Sixthsense :)]

Data Range

	A
1	BA 12549
2	BA 12550
3	BA 12551
4	BA 12552
5	BA 12553
6	BA 12554

Data Range

	B	C
1	12554	12549

Data Range

	B	C
1	=MAX(IFERROR(VALUE(MID(A1:A6&" ",FIND(" ",A1:A6)+1,255)),0))	=MIN(IFERROR(VALUE(MID(A1:A6&" ",FIND(" ",A1:A6)+1,255)),9^9))

Both are array formula's so confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

[rcahayag1925]

thank you, quekbc

[rcahayag1925]

sixthsense, 255 means?

[rcahayag1925]

suppose this data was a result of extracting a formula from another sheet, example

BA 12549 (from sheet 1)
BA 12550 (sheet 2)
BA 12551 (sheet 3)
BA 12552 (sheet 4)
BA 12553 (sheet 5)
BA 12554 (sheet 6)
Debit (sheet 7)
Credit (sheet 8)

i want a MIN of 12549 and a MAX of 12554

[Glenn Kennedy]

Are all these numbers meant to be on the same sheet (copied/extracted from other sheets)?

BA 12549 (from sheet 1)
BA 12550 (sheet 2)
BA 12551 (sheet 3)
BA 12552 (sheet 4)
BA 12553 (sheet 5)
BA 12554 (sheet 6)
Debit (sheet 7)
Credit (sheet 8)

Are "Debit" and "Credit" always the last two entries in the list?
Are "Debit" and "Credit" ALWAYS non-numerical?

[:) Sixthsense :)]

suppose this data was a result of extracting a formula from another sheet,
BA 12549 (from sheet 1)

Revised formula for the above scenario

=MAX(IFERROR(VALUE(TRIM(MID(TRIM(A1:A6)&" ",FIND(" ",TRIM(A1:A6))+1,IFERROR(FIND("(",TRIM(A1:A6))-FIND(" ",TRIM(A1:A6))-1,255)))),0))

=MIN(IFERROR(VALUE(TRIM(MID(TRIM(A1:A6)&" ",FIND(" ",TRIM(A1:A6))+1,IFERROR(FIND("(",TRIM(A1:A6))-FIND(" ",TRIM(A1:A6))-1,255)))),9^9))

[:) Sixthsense :)]

sixthsense, 255 means?

Just a default end value

[rcahayag1925]

thank you sixthsense

[rcahayag1925]

Glenn Kennedy, these numbers are on same sheet now after being extracted from other sheets

debit/credit are always non-numerical, these too, may appear anywhere (in the beginning, middle or last) in no order

thank you

[Glenn Kennedy]

Thanks, but I don't think it matters anymore: you seem to have an excellent solution already!!

[rcahayag1925]

sixthsense, the value yields to #NAME?

[:) Sixthsense :)]

sixthsense, the value yields to #NAME?

What is your excel version?

[rcahayag1925]

What is your excel version?

excel 2003

[quekbc]

Real quick question, in your table, you said

BA 12549 (from sheet 1)
BA 12550 (sheet 2)

Are those texts (from sheet 1) actually showing in that cell?

[samba_ravi]

If you attached your excel file as said earlier, your query had solved in a long back.

[rcahayag1925]

Real quick question, in your table, you said

BA 12549 (from sheet 1)
BA 12550 (sheet 2)

Are those texts (from sheet 1) actually showing in that cell?

quekbc, it is now working.. my problem now if it contains non-numerical data, example, Debit, Credit, etc
it yield to #VALUE!

and if the data with a zero(0) on the beginning of the number.
example, BA 01234, BA 01235, etc.. using the formula you have provided, it results to 1234 instead of 01234

[quekbc]

rcahayag1925, don't mind me. I was looking through SixthSense's solution and I thought that it seems to require the need of "(" within the table of data that you have. However, the IFERROR there seems to have already handled it.

Although, if you can confirm/deny my earlier question, the formula can be simplified further.

With regards to the numbers, you can change that using formatting. Right click on the cell, Format Cells, Number tab, select Custom, and type in Type: 00000.

[rcahayag1925]

rcahayag1925, don't mind me. I was looking through SixthSense's solution and I thought that it seems to require the need of "(" within the table of data that you have. However, the IFERROR there seems to have already handled it.

Although, if you can confirm/deny my earlier question, the formula can be simplified further.

With regards to the numbers, you can change that using formatting. Right click on the cell, Format Cells, Number tab, select Custom, and type in Type: 00000.

thank you, quekbc

[:) Sixthsense :)]

excel 2003

=max(if(iserr(value(trim(mid(trim(a1:a8)&" ",find(" ",trim(a1:a8))+1,if(iserr(find("(",trim(a1:a8))-find(" ",trim(a1:a8))-1),255,find("(",trim(a1:a8))-find(" ",trim(a1:a8))-1))))),0,value(trim(mid(trim(a1:a8)&" ",find(" ",trim(a1:a8))+1,if(iserr(find("(",trim(a1:a8))-find(" ",trim(a1:a8))-1),255,find("(",trim(a1:a8))-find(" ",trim(a1:a8))-1))))))

=min(if(iserr(value(trim(mid(trim(a1:a8)&" ",find(" ",trim(a1:a8))+1,if(iserr(find("(",trim(a1:a8))-find(" ",trim(a1:a8))-1),255,find("(",trim(a1:a8))-find(" ",trim(a1:a8))-1))))),9^9,value(trim(mid(trim(a1:a8)&" ",find(" ",trim(a1:a8))+1,if(iserr(find("(",trim(a1:a8))-find(" ",trim(a1:a8))-1),255,find("(",trim(a1:a8))-find(" ",trim(a1:a8))-1))))))

[Glenn Kennedy]

or this (????) in Excel 2003 (array entered). I'll be amazed if it's OK for all situations as it's so short, by comparison with Sixthsense's formula, above!!

Max =MAX(IF(ISERROR(--MID(A1:A8,FIND(" ",A1:A8)+1,255)),"",--MID(A1:A8,FIND(" ",A1:A8)+1,255)))

Min =MIN(IF(ISERROR(--MID(A1:A8,FIND(" ",A1:A8)+1,255)),"",--MID(A1:A8,FIND(" ",A1:A8)+1,255)))



Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

It SEEMS to be OK.

[rcahayag1925]

or this (????) in Excel 2003 (array entered). I'll be amazed if it's OK for all situations as it's so short, by comparison with Sixthsense's formula, above!!

Max =MAX(IF(ISERROR(--MID(A1:A8,FIND(" ",A1:A8)+1,255)),"",--MID(A1:A8,FIND(" ",A1:A8)+1,255)))

Min =MIN(IF(ISERROR(--MID(A1:A8,FIND(" ",A1:A8)+1,255)),"",--MID(A1:A8,FIND(" ",A1:A8)+1,255)))



Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

It SEEMS to be OK.

thank you, glenn..its working

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.