Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy cell values (derived from formula), not references? | Excel Worksheet Functions | |||
column values to a cell with comma seperated | Excel Worksheet Functions | |||
VLookup to sum cell values | Excel Worksheet Functions | |||
cell color index comparison | New Users to Excel | |||
up to 7 functions? | Excel Worksheet Functions |