A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Min/Max of Numbers as Text



 
 
Thread Tools Display Modes
  #1  
Old October 2nd, 2009, 01:56 PM posted to microsoft.public.excel.worksheet.functions
Todd
external usenet poster
 
Posts: 377
Default Min/Max of Numbers as Text

I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.
  #2  
Old October 2nd, 2009, 02:04 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Min/Max of Numbers as Text

Try the below array formula. you press CTRL+SHIFT+ENTER to enter the formula.
If successful in 'Formula Bar' you can notice the curly braces at both ends
like "{=formula}"

=MIN(IF(A1:A10"",VALUE(A1:A10)))

If this post helps click Yes
---------------
Jacob Skaria


"Todd" wrote:

I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

  #3  
Old October 2nd, 2009, 02:05 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Min/Max of Numbers as Text

Try something like this ..
array-entered (ie press CTRL+SHIFT+ENTER to confirm the formula):
=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Todd" wrote:
I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

  #4  
Old October 2nd, 2009, 02:32 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default Min/Max of Numbers as Text

=TEXT(MIN(INDEX(A1:A5+0,)),"00000")

Just press ENTER


"Todd" wrote:

I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

  #5  
Old October 2nd, 2009, 03:26 PM posted to microsoft.public.excel.worksheet.functions
Todd
external usenet poster
 
Posts: 377
Default Min/Max of Numbers as Text

This is exactly what I was looking for. Thank you.

"Max" wrote:

Try something like this ..
array-entered (ie press CTRL+SHIFT+ENTER to confirm the formula):
=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Todd" wrote:
I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

  #6  
Old October 2nd, 2009, 03:27 PM posted to microsoft.public.excel.worksheet.functions
Todd
external usenet poster
 
Posts: 377
Default Min/Max of Numbers as Text

Your formula did the trick. However, another posting had a slightly more
simplified formula that achieves the same result (see below).

=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")

Thanks so much!

"Teethless mama" wrote:

=TEXT(MIN(INDEX(A1:A5+0,)),"00000")

Just press ENTER


"Todd" wrote:

I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

  #7  
Old October 2nd, 2009, 03:29 PM posted to microsoft.public.excel.worksheet.functions
Todd
external usenet poster
 
Posts: 377
Default Min/Max of Numbers as Text

Your formula comes very close except it returns the result as a value and I
need to keep the result text. Another response gave me the formulas below
that keep the result as text. Thought I would share it with you. Thank you
very much for your help.

=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")


"Jacob Skaria" wrote:

Try the below array formula. you press CTRL+SHIFT+ENTER to enter the formula.
If successful in 'Formula Bar' you can notice the curly braces at both ends
like "{=formula}"

=MIN(IF(A1:A10"",VALUE(A1:A10)))

If this post helps click Yes
---------------
Jacob Skaria


"Todd" wrote:

I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

  #8  
Old October 2nd, 2009, 03:43 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default Min/Max of Numbers as Text

=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")


Those formula are required CTRL+SHIFT+Enter, my formula just press ENTER.



"Todd" wrote:

Your formula did the trick. However, another posting had a slightly more
simplified formula that achieves the same result (see below).

=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")

Thanks so much!

"Teethless mama" wrote:

=TEXT(MIN(INDEX(A1:A5+0,)),"00000")

Just press ENTER


"Todd" wrote:

I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

  #9  
Old October 2nd, 2009, 03:50 PM posted to microsoft.public.excel.worksheet.functions
Todd
external usenet poster
 
Posts: 377
Default Min/Max of Numbers as Text

Thanks for pointing that out. I didn't catch that. I never know how many
codes will be in column B so I changed your formula to be as shown below and
it works great (plus it's a "regular" formula because few people in my area
understand array formulas). Thanks again.

=TEXT(MIN(INDEX(INDIRECT("B2:B"&COUNTA(B2:B65536)+ 1)+0,)),"00000")

"Teethless mama" wrote:

=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")


Those formula are required CTRL+SHIFT+Enter, my formula just press ENTER.



"Todd" wrote:

Your formula did the trick. However, another posting had a slightly more
simplified formula that achieves the same result (see below).

=TEXT(MIN(A1:A5+0),"00000")
=TEXT(MAX(A1:A5+0),"00000")

Thanks so much!

"Teethless mama" wrote:

=TEXT(MIN(INDEX(A1:A5+0,)),"00000")

Just press ENTER


"Todd" wrote:

I have a list of identification codes that are five digit numbers but will
always be treated as text. For example,

'00020
'00015
'00025
'00050
'00035

I need a worksheet function that will tell me the MIN and MAX of these codes
(evaluating the text as numbers). In this example I know that MIN=00015 and
MAX=00050. However, the actual list will contain hundreds or thousands of
codes. I need the solution to be a function. Sorting or filtering to
manually find the MIN/MAX will not work for me. I'm sure I'm not the first
person to encounter this so I am hoping someone can help me. Thanks so much.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.