How to slot cell values into pre-defined ranges
 

I would like to slot cell values starting from 500 upto 75000 in ranges
(bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have 15
bins.

pls help

Not sure what you want here. Do you want to count the number of times values
within a range occur?

--
Ian
--
"KDD" wrote in message
...
I would like to slot cell values starting from 500 upto 75000 in ranges
(bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have
15
bins.

pls help

Ian,

My D column in the database has values ranging from 500 to 75000. I want to
E to return these values by putting them in pre-defined bins (ranges) of
500-999, 1000-1499, 1500-1999 etc.
--
KDDXB


"Ian" wrote:

Not sure what you want here. Do you want to count the number of times values
within a range occur?

--
Ian
--
"KDD" wrote in message
...
I would like to slot cell values starting from 500 upto 75000 in ranges
(bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have
15
bins.

pls help

In E2, =INT(D2/500)*500&"-"&INT(D2/500)*500+499 will return the eg 1500-1999
for any value between in this range.

--
Ian
--
"KDD" wrote in message
...
Ian,

My D column in the database has values ranging from 500 to 75000. I want
to
E to return these values by putting them in pre-defined bins (ranges) of
500-999, 1000-1499, 1500-1999 etc.
--
KDDXB


"Ian" wrote:

Not sure what you want here. Do you want to count the number of times
values
within a range occur?

--
Ian
--
"KDD" wrote in message
...
I would like to slot cell values starting from 500 upto 75000 in ranges
(bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I
have
15
bins.

pls help

Ian, unless i have misunderstood, the solution you provided may not work.

See, D2 can be any value from 500 to 75000. I have D2 upto D2500. My ranges
(bins) are pre-defined. So E2 has to recognize in which bin D2 and
accordingly return either the range (e.g. 500-999) or the median of the range
e.g. 750.

thanks
--
KDDXB


"KDD" wrote:

Ian,

My D column in the database has values ranging from 500 to 75000. I want to
E to return these values by putting them in pre-defined bins (ranges) of
500-999, 1000-1499, 1500-1999 etc.
--
KDDXB


"Ian" wrote:

Not sure what you want here. Do you want to count the number of times values
within a range occur?

--
Ian
--
"KDD" wrote in message
...
I would like to slot cell values starting from 500 upto 75000 in ranges
(bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have
15
bins.

pls help

My formula works as follows: If D2=699 E2 will be 500-999. If D2=1945 E2
will be 1500-1999. Isn't this what you meant?

--
Ian
--
"KDD" wrote in message
...
Ian, unless i have misunderstood, the solution you provided may not work.

See, D2 can be any value from 500 to 75000. I have D2 upto D2500. My
ranges
(bins) are pre-defined. So E2 has to recognize in which bin D2 and
accordingly return either the range (e.g. 500-999) or the median of the
range
e.g. 750.

thanks
--
KDDXB


"KDD" wrote:

Ian,

My D column in the database has values ranging from 500 to 75000. I want
to
E to return these values by putting them in pre-defined bins (ranges) of
500-999, 1000-1499, 1500-1999 etc.
--
KDDXB


"Ian" wrote:

Not sure what you want here. Do you want to count the number of times
values
within a range occur?

--
Ian
--
"KDD" wrote in message
...
I would like to slot cell values starting from 500 upto 75000 in
ranges
(bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I
have
15
bins.

pls help

Yes i did. I just tried out the formula, and it works perfect when the ranges
are equal.

However, my mistake, i missed out mentioning that the ranges are not equal.
Some are 500-999 (difference of 499) and others are 15000-29999(diff of
14999).
--
KDDXB


"Ian" wrote:

My formula works as follows: If D2=699 E2 will be 500-999. If D2=1945 E2
will be 1500-1999. Isn't this what you meant?

--
Ian
--
"KDD" wrote in message
...
Ian, unless i have misunderstood, the solution you provided may not work.

See, D2 can be any value from 500 to 75000. I have D2 upto D2500. My
ranges
(bins) are pre-defined. So E2 has to recognize in which bin D2 and
accordingly return either the range (e.g. 500-999) or the median of the
range
e.g. 750.

thanks
--
KDDXB


"KDD" wrote:

Ian,

My D column in the database has values ranging from 500 to 75000. I want
to
E to return these values by putting them in pre-defined bins (ranges) of
500-999, 1000-1499, 1500-1999 etc.
--
KDDXB


"Ian" wrote:

Not sure what you want here. Do you want to count the number of times
values
within a range occur?

--
Ian
--
"KDD" wrote in message
...
I would like to slot cell values starting from 500 upto 75000 in
ranges
(bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I
have
15
bins.

pls help

Try this:

=IF(D2<15000,INT(D2/500)*500+250,INT(D2/15000)*15000+7500)

I've shortened the formula but displaying the median instead of the range.
I'm assuming that up to 14999, the ranges are 500, then 15000 after that.

--
Ian
--
"KDD" wrote in message
...
Yes i did. I just tried out the formula, and it works perfect when the
ranges
are equal.

However, my mistake, i missed out mentioning that the ranges are not
equal.
Some are 500-999 (difference of 499) and others are 15000-29999(diff of
14999).
--
KDDXB


"Ian" wrote:

My formula works as follows: If D2=699 E2 will be 500-999. If D2=1945 E2
will be 1500-1999. Isn't this what you meant?

--
Ian
--
"KDD" wrote in message
...
Ian, unless i have misunderstood, the solution you provided may not
work.

See, D2 can be any value from 500 to 75000. I have D2 upto D2500. My
ranges
(bins) are pre-defined. So E2 has to recognize in which bin D2 and
accordingly return either the range (e.g. 500-999) or the median of the
range
e.g. 750.

thanks
--
KDDXB


"KDD" wrote:

Ian,

My D column in the database has values ranging from 500 to 75000. I
want
to
E to return these values by putting them in pre-defined bins (ranges)
of
500-999, 1000-1499, 1500-1999 etc.
--
KDDXB


"Ian" wrote:

Not sure what you want here. Do you want to count the number of
times
values
within a range occur?

--
Ian
--
"KDD" wrote in message
...
I would like to slot cell values starting from 500 upto 75000 in
ranges
(bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this?
I
have
15
bins.

pls help