Distribute numbers into buckets

[targus92]

Hi everyone -

can you please take a look at the file attached. You will see a column for store number and a column for distance in miles. I want to be able to distribute all the stores evenly into 6 or 7 buckets based on distance. I don't know what the bucket headers should be so that they are distributed evenly and I need a formula to link column F to see which bucket it should lie in. So for example, store#3009 should have "No's" in every bucket except for column K under "2-3" because 2.4 falls in that bucket. And then under the Bucket column in column E I need it to link a reference so it tells me under which bucket does it fall in .

Please help i know there must be a quick formulas.

Thank you so much

[targus92]

Ideally - I would want a count of how many stores fall into each bucket please

[shg]

Row\Col	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Store #		Distance in Miles			0	1	2	3	5	10	15	50
3	3009		2.4					x						I3: =IF(LOOKUP($F3, $I$2:$P$2) = I$2, "x", "")
4	3013		3.3						x
5	3016		0.2			x
6	3018		0.6			x
7	3021		0.4			x
8	3022		17.6									x
9	3027		3.6						x
10	3028		5.0							x

[FlameRetired]

And then under the Bucket column in column E I need it to link a reference so it tells me under which bucket does it fall in .

Piggybacking on shg's example put this in E3 and fill down.

Please Login or Register  to view this content.

Row\Col	D	E	F	G	H	I	J	K	L	M	N	O	P
1						0	1	2	3	5	10	15	50
2	Store #		Distance in Miles			0-1	1-2	2-3	3-5	5-10	10-15	15-50	50+
3	3009	2-3	2.4					X
4	3013	3-5	3.3						X
5	3016	0-1	0.2			X
6	3018	0-1	0.6			X
7	3021	0-1	0.4			X

[targus92]

Hi guys,

thank you for the prompt responses. Dave quick question - or if anyone can answer. Is there a count formula where I can count in Column E how many "2-3"s there are how many 3-5"s there are or or 0-1s there are?

[FlameRetired]

Yes.

In I1 of shg's example (new row 1 inserted) and filled across

Please Login or Register  to view this content.

Row\Col	D	E	F	G	H	I	J	K	L	M	N	O	P
1						1057	155	137	271	351	119	202	53
2						0	1	2	3	5	10	15	50
3	Store #		Distance in Miles			0-1	1-2	2-3	3-5	5-10	10-15	15-50	50+
4	3009	2-3	2.4					X
5	3013	3-5	3.3						X
6	3016	0-1	0.2			X
7	3018	0-1	0.6			X
8	3021	0-1	0.4			X