How to slot cell values into pre-defined ranges

[KDD]

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]

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

--
Ian
--
"KDD" <[email protected]> wrote in message
news:[email protected]...
>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
>

[KDD]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>> >
>>
>>
>>

[KDD]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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
>> > >
>> >
>> >
>> >

[KDD]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > >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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > >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
>> >> > >
>> >> >
>> >> >
>> >> >
>>
>>
>>