Number Range Format Type

  • Thread starter Thread starter Nobody
  • Start date Start date
N

Nobody

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

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

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
 
Back
Top