Number Range Format Type

I want to have a format for my cells that basically means "the data in
this cell specifies a range of integers".  For example, it might say
1-4, or 2-7, etc.  Or it might just say 1 which is the same as 1-1
(low val is 1 and high val is 1).  Is there a way I can have a format
code for this and use a custom format?  Later on in the worksheet, I
want to be able to extract the min and max values of this range from
the cells.  The application of this is that a certain column is going
to be used for holding ranges of numbers.  Then at the very bottom I
want to add up the ranges to produce the overall range.  Like if my
column is

4-7
2-3
1-6
8-9

I want to add all this up and have a cell which contains the value

15-25

Can someone help me out?

Sincerely,
Zachary Turner
0
8/19/2004 6:29:54 PM
excel 39879 articles. 2 followers. Follow

2 Replies
336 Views

Similar Articles

[PageSpeed] 39

On 19 Aug 2004 11:29:54 -0700, toolshed37@yahoo.com (Nobody) wrote:

>I want to have a format for my cells that basically means "the data in
>this cell specifies a range of integers".  For example, it might say
>1-4, or 2-7, etc.  Or it might just say 1 which is the same as 1-1
>(low val is 1 and high val is 1).  Is there a way I can have a format
>code for this and use a custom format?  Later on in the worksheet, I
>want to be able to extract the min and max values of this range from
>the cells.  The application of this is that a certain column is going
>to be used for holding ranges of numbers.  Then at the very bottom I
>want to add up the ranges to produce the overall range.  Like if my
>column is
>
>4-7
>2-3
>1-6
>8-9
>
>I want to add all this up and have a cell which contains the value
>
>15-25
>
>Can someone help me out?
>
>Sincerely,
>Zachary Turner

Your options depend on your data.

If the high and low numbers are always single digits, then you could enter them
as a number and format the cell as Format/Number/Custom Type:  0-0

So you would enter 47 and it would display as 4-7.

If that's not the case, then probably the best format to use would be text, and
enter the range as you see it:  '4-7 for example.

Then use formulas to get at the first part and the second part.

The *array-entered* formula   --LEFT(A1:A4,FIND("-",A1:A4)-1) will give an
array of numbers corresponding to the first part of each entry.  You can then
use SUM, MIN or MAX on that array.

The *array-entered* formula  --MID(A1:A4,FIND("-",A1:A4)+1,255)  will give the
second part of the range.

So, to get the sums separated by a hyphen, one could use the *array-entered*
formula:

=TEXT(SUM(--LEFT(A1:A4,FIND("-",A1:A4)-1)),"0-")&
SUM(--MID(A1:A4,FIND("-",A1:A4)+1,255))

and so on.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.


--ron
0
ronrosenfeld (3122)
8/19/2004 6:50:35 PM
Ron Rosenfeld <ronrosenfeld@nospam.org> wrote in message news:<h8t9i0t0vp3i68gal97m4uustg0kr1ip8d@4ax.com>...
> On 19 Aug 2004 11:29:54 -0700, toolshed37@yahoo.com (Nobody) wrote:
> 
> >I want to have a format for my cells that basically means "the data in
> >this cell specifies a range of integers".  For example, it might say
> >1-4, or 2-7, etc.  Or it might just say 1 which is the same as 1-1
> >(low val is 1 and high val is 1).  Is there a way I can have a format
> >code for this and use a custom format?  Later on in the worksheet, I
> >want to be able to extract the min and max values of this range from
> >the cells.  The application of this is that a certain column is going
> >to be used for holding ranges of numbers.  Then at the very bottom I
> >want to add up the ranges to produce the overall range.  Like if my
> >column is
> >
> >4-7
> >2-3
> >1-6
> >8-9
> >
> >I want to add all this up and have a cell which contains the value
> >
> >15-25
> >
> >Can someone help me out?
> >
> >Sincerely,
> >Zachary Turner
> 
> Your options depend on your data.
> 
> If the high and low numbers are always single digits, then you could enter them
> as a number and format the cell as Format/Number/Custom Type:  0-0
> 
> So you would enter 47 and it would display as 4-7.
> 
> If that's not the case, then probably the best format to use would be text, and
> enter the range as you see it:  '4-7 for example.
> 
> Then use formulas to get at the first part and the second part.
> 
> The *array-entered* formula   --LEFT(A1:A4,FIND("-",A1:A4)-1) will give an
> array of numbers corresponding to the first part of each entry.  You can then
> use SUM, MIN or MAX on that array.
> 
> The *array-entered* formula  --MID(A1:A4,FIND("-",A1:A4)+1,255)  will give the
> second part of the range.
> 
> So, to get the sums separated by a hyphen, one could use the *array-entered*
> formula:
> 
> =TEXT(SUM(--LEFT(A1:A4,FIND("-",A1:A4)-1)),"0-")&
> SUM(--MID(A1:A4,FIND("-",A1:A4)+1,255))
> 
> and so on.
> 
> To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>.
> Excel will place braces {...} around the formula.
> 
> 
> --ron

I gave up and just had it be a text cell and I assume the user enters
it correctly.  I wrote a custom function to exact the min and max, but
yours also works too.  Thanks
0
8/20/2004 5:39:11 PM
Reply:

Similar Artilces:

Numbering
Can I enter numbers in a cross functional flowchart? On Mon, 24 Aug 2009 12:07:02 -0700, carosaam <carosaam@discussions.microsoft.com> wrote: >Can I enter numbers in a cross functional flowchart? Yes. Do you mean for the process names or function names? Yes. -- Regards, Paul Herber, Sandrila Ltd. Electronics for Visio http://www.electronics.sandrila.co.uk/ Electrical for Visio http://www.electrical.sandrila.co.uk/ Electronics Packages for Visio http://www.electronics-packages.sandrila.co.uk/ ...

How do I expand the number of characters in Access comment box?
I am using Access 2007 and want to be able to include more than 250 characters in a "Comment" box. Hi, You could try changing your table's field type to Memo. Assuming that your are talking about a field in a table, that maybe shows on a form. If that is not the case, please describe precisely what comment box your are attempting to use. Clifford Bass Pearlene wrote: >I am using Access 2007 and want to be able to include more than 250 >characters in a "Comment" box. -- Message posted via http://www.accessmonster.com ...

negative number to positive number
How can I change a negative number to positive number Multiply by -1 or use the ABS() function. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fgiord" <fgiord@discussions.microsoft.com> wrote in message news:62C2A6DA-7AC2-4379-AEFC-B3D6F3698E58@microsoft.com... > How can I change a negative number to positive number =ABS(##) or mult it by -1 -- Regards Rob "fgiord" <fgiord@discussions.microsoft.com> wrote in message news:62C2A6DA-7AC2-4379-AEFC-B3D6F3698E58@microsoft.com... > How can I chan...

Hide page numbers for mutliple discontiquous levels
Hi: I need to hide the page numbers for levels 1 and 7 in my TOC. I used the \n switch to hide level 1, but cannot find a way to specify multiple ranges for this switch. Is there a way to do this? I am using Word 2003. Phil You have to use a trick. See the "Omitting page numbers for noncontiguous levels" section of http://sbarnhill.mvps.org/WordFAQs/TOCTips.htm#OmitPageNumbers -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Phillip Llacuna" <phillip.v.llacuna@lmco.com> wrote in me...

1-52 pages, 8,000 employee numbers
Dear Excel, I have the ultimate challenge: to do job books for 8,000 employees. The form is completed, but the request is to make booklets of 52 pages for each employee. Each page of the booklet must also have each employee's number on it (0001 to 8000). Each page o fthe booklet must be numbered with the week number from 01 to 52. Therefore I need to auto generate 416,000 pages. Ridiculous I know but nobody listens to us! How can I export the above scenario to PDF from Excel? Thanks On Apr 19, 2:17=A0pm, gatecrasherg13 gatecrasherg13 <gatecrasher...@gmail.com> wr...

V-Lookup Data Formats Imported from Crystal
When I use data imported from Crystal in a V-Lookup, it appears to be in number or general format, but the lookup function fails. I can make it work cell by cell if I copy from one list to another, but that is slow! Any way to get imported data into the correct format automatically? -- Hudd Hi try after importing: - select an empty cell and copy it - select your importted data - goto 'Edit - Paste Special' and choose 'Add' -- Regards Frank Kabel Frankfurt, Germany Hudd wrote: > When I use data imported from Crystal in a V-Lookup, it appears to be > in number or ...

Sequential numbering in Number field
When I am entering transactions manually, I will put "Debit" or "VISA" to indicate how the withdrawl was made. When I download my account information, Money05 overwrites this with a sequential number. I have to go into the entry a second time to redo my change. Does anyone know how to turn this "feature" off? I want my values used - sequential numbering does not help me in the slightest. This is not a "feature" you can turn off. It's a reflection of the basic premise of downloaded transaction data. The presumption with downloading data...

How to refer to a range except one cell
Hi, I'm trying to average a range except one cell in the middle of that range (or alternatively to condition on a range made of two separate sections). The formula I'm trying to use is the following: =IF(AND(B$47>0, B$53=2), B$47-AVERAGEIF(B$45:B$50, ">0"), "-") Except, instead of B45:B50 in AVERAGEIF, I want the range to be B45:B46 and B48:B50 (i.e. without the cell B47). Can anyone tell me how to use a split range with AVERAGEIF or how to tell it not to look at cell B47? Grateful for any advice. Thanks! Just use some helper cells:...

Cant find the bugs in the code
I am grateful to any help and suggestions what to do! Here ar the subs I expect to make the users able to copy and paste between and within sheets. It doesn't work properly. Sometimes when I change som parts, it works partly, but never completly: - Not allow draganddrop - On every change of a range or cell an new fresh format template sheet shall be pastespecial(xlpasteFormulas) to be able to hadle merged cells in clipboard etc. -Not create a loop - Keep the sheet1 protected Please - what is wrong? i have tried to use less selection & activate code, but i could't make it right eit...

How to define cells so that only numbers can be filled?
Hi, How could I format cells in excel so that only numbers can be filled The input should be integer e.g. between 0...99 and cells shoud no accept any other marks, such as space, letters... simply nothing els but integers. Thanks -- Message posted from http://www.ExcelForum.com Hi Juha! Try: Data > Validation You'll find it pretty intuitive and very flexible as to what you can do as far a restricting input to a cell. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available fre...

Updating formulas (Range)
Hi All, I was wondering if anyone new a way to update a formula using a range. For example: Currently I have a formula that is as follows: =IF ($C$4=202,C$4=12400,$C$4=12703,$C$4=34007,$C$4=50116,$C$4=50702,$C$4=61100)),"LA","N/A") Each month the numbers (i.e. 202, 12400, 12703, etc.) change. Ther can be anywhere from 4 to 40 of these numbers which I manually ente into the formula each month. Is there a way to have the formula loo at a range of numbers to check if any matches $C$4 so I can jus reference a range rather than typing the numbers in the formula eac month...

Excel 2008 hyperlinks won=?ISO-8859-1?Q?=92?=t save on .xlsx format, but will save in xls format.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel xcel 2008 hyperlinks won't save on .xlsx format, but will save in xls format. <br><br>After the workbook is saved and reopened the hyperlink give the error message "Reference is not valid". <br><br>I have cleared all links and hyperlinks and replaced the hyperlinks a number of time with the same results. > Make sure you have applied all the updates top Office. I think this one was <br> > solved in a service pack. <br> > <br> > <br>...

Number of Cells in a column
Hi, I want to add this formula in my ESS. COUNTIF(sheet1!S2:S43,"*Yes*") to find out the number of value containing "YES". My concern is I don't have fixed count from S2 to S43. It could be something else. What can I do to have this code reusable? I thought something like COUNTIF(Sheet!S2:Count($S2),"*Yes*") \But it doesnt work Thanks for your help Jack Try this if there is nothing else in that column:- =COUNTIF(sheet1!S:S,"*Yes*") -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - ...

General Number Format Changed
Excel 2003 on XP I have a collegue who's Excel setting for General number format displays as 0.1 instead of 1 in the sample box. ie if you type in "1" you get "0.1", if you type in ".1" (as in 0.1) you get "1". Where in the settings can this General format be returned to standard? Many thanks DeanH Sorry that should have been: ie if you type in "1" you get "0.1", if you type in "1." (as in 1.0) you get "1". "DeanH" wrote: > Excel 2003 on XP > I have a collegue who's Excel setting for G...

Numbering, revisited
While there are already a lot of posts about numbering, I haven't been able to find the solutions for my specific quandry... [BTW, I'm posting this under "General" even though ultimately I'd like to make macros to support the answer -- since a manual solution needs to be the first step! :) ] Background: My workgroup is preparing to migrate to Word 2007 very soon. We create/maintain hundreds of large procedure manuals, each containing multiple chapters (sometimes up to 40-50 per manual). Each chapter consists of steps, using multi-level numbered lists...

transaction number in PC Charge
Is there any way to manage the fields that should go to PC Charge while tendering credit cards in RMS. The problem is that transaction number ( called Ticket in PC Charge) doesn't appear in PC Charge and that cost as extra money. Regards, Ewa ...

Limit number of characters in a cell
Hi, Is there a way to limit the number of characters that a user can key into a cell. I want to get a list of names and addresses imputted by user, but I don't want them to be more than 35 characters. Thanks for the help Dr. Senji Take a look at Data|Validation. You can have excel yell at the user when they hit enter after typing in a too-long string. Dr Senji wrote: > > Hi, > > Is there a way to limit the number of characters that a user can key into a > cell. > > I want to get a list of names and addresses imputted by user, but I don't > want the...

Case, Quote numbering
We should have the ability to number the cases without having a suffix. If we do have a suffix, we should be able to define the suffix. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defa...

Rounding numbers to the nearest thousand
Simple question (i hope) Is there a simple way i can round numbers to the nearest thousand by using the format cells command. For example i would like to display 1,234.56 as 12 I don't want to be diving by 1000 and rounding to zero decimal places because i still want the whole number displayed in the formula bar when selecting it. i use excel 2003 thanx Mark Format the number as #,##0,;-#,##0, or similar. Not the comma at the end - this tells Excel to display as thousands (two commas here will display as millions). Note that 1,234.56 will display as 1 (thousand) not 12. "...

Account type
I have Money 2004 but i first had 2002 and i think i setup my accounts wrong i have a checking and savings account but money shows them as bank accounts how do i change them to read checking and savings accounts i tried going to setup again and changing he details but there is no option for checking or savings accounts. Accounts created will only show one of a few options as an account type in their account details, irrespective of what you selected originally - Bank, Credit Card, Cash or Other, Asset and Liability are the options on my version [Investment accounts don't have the o...

Form of phone numbers in contact list
I am using Blackberry's Desktop Software to syncronize with my MS Outlook Contacts list. The onboard caller-id function of the phone that is supposed to correlate the incoming phone number to a name of a contact (should the number be in my contacts list) isn't working. According to help from Blackberry the problem is the "form" of the numbers as they're being loaded on the Blackberry from MS Outlook. Specifically, apparently the fact that the numbers are stored in the form of (###) ###-#### instead of something like ###-###-#### is keeping the phone from realizi...

Formula for Dynamic Range?
I am inexperienced and need help: Need to create formula for: A B C D E F Row1 1 2 3 4 5 6 Row2 2 4 6 8 10 12 Row3 Needs to include formula that results in range changes in row2 based on the value cell in row1. I do not have an idea of what to use. Thanks > Row3 > Needs to include formula that results in > range changes in row2 based on the value cell in row1. Can you provide some sample values of what should appear in A3, B3, ... F3 given the values in A1:F1 and in A2:F2, and explain the logic behind how these values are computed? -- Max Singapore http://savefile.com/pro...

Message Box Formating
Is there a procedure that can be used to change the font and background colors for message boxes? -- Enjoy the blessings of the day. jerry Jerry, No, you can't change the font or colors of a message box. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Gerald Evans" <gerald.j.evans@worldnet.att.net> wrote in message news:vkaod.950527$Gx4.387440@bgtnsc04-news.ops.worldnet.att.net... > Is there a procedure that can be used to change the font and > background > colors for message boxes? > > -- >...

sorting numbers and numbers that contain text in excel
A column contains both strictly numbers and also numbers that are followed by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a) Identical numbers are related documents, with the text suffixes referring to addenda documents; thus, document 1000 has an addendum document 1000a; How can I sort the column so in the following order: row 1 (1000), row 3 (1000a), row 2 (1500), row 4 (1500a)? Thank you -- MZ =TEXT(A1,"0") will turn each into text, then sort by that helper column (and don't accept Excel's suggestion to treat text that looks like number...

how to format date to have a specific format
Hi. I have a query that search between dates. But once the main program from where my linked has Date and time together I had to make a short date: I had this: 04-10-2007 08:35:44 with the short date I have this: 04-10-2007 the problem is that when I insert the criteria the date: 04-10-2007 to list all records of 04-100-2007 the access automatically transform that in: 4-10-2007 I went to the regional settings and the settings are: dd-MM-yyyy The criteria value is a value that comes from a form such as: [forms]![chart]![dateone] What can I do to force the criteria to use: short date...