Bin Count Distrubution Formula???? Normalized Bin Intervals???

[mrmmickle1]

I have a large set of data that has Sales Volume in it by State. I want to disperse values into bins based on the distribution. I have cut the data by month and by year. This means that for each month the distribution of Sales Volume will change. I am trying to get a formula that will tell me a good Bin interval. SO the Distribution of Values is in Column D. Right Now I am using trial and error Countif formulas to try to determine the bins based on the values in Column F and G. Is there a formula way to optimize the bin intervals..... In other words get a good distribution of values in all of the bins. So that my Color Gradient will not all be one color. For Example if I set the starting threshold at 100000 then all of my colors would be Dark Red (

2,490,533)... This would not be a good distribution.... I want to display as many colors as possible. So I am looking for a good way to determine the thresholds.. Any Advice or assistance would be greatly appreciated.
​

Excel 2013
	A	B	C	D	E	F	G	H	I
1	AL	43601		Distribution		>	<	ActiveCell.Interior.Color	Threshold
2	AR	564		15			100	3,631,104	0
3	AZ	50		3		101	250	5,281,818	100
4	CA	2677		2		251	500	6,536,550	250
5	CO	598		10		501	2500	7,002,534	500
6	CT	231		3		2501	7500	9,170,905	2500
7	DC	20		9		7501	20000	9,167,102	7500
8	DE	0		4		20001	40000	6,401,789	20000
9	FL	57137		2		40001	60001	4,419,060	40000
10	GA	12862		0		60001	95000	4,419,060	60000
11	IA	197		1		95001	100000	2,490,533	95000
12	ID	0
13	IL	8306
14	IN	10049
15	KS	222
16	KY	28491
17	LA	2184
18	MA	9925
19	MD	1780
20	ME	3
21	MI	25703
22	MN	0
23	MO	3551
24	MS	2113
25	MT	0
26	NC	24093
27	ND	0
28	NE	1564
29	NH	346
30	NJ	2257
31	NM	7977
32	NV	71
33	NY	0
34	OH	10182
35	OK	1584
36	OR	1545
37	PA	19525
38	RI	12
39	SC	14785
40	SD	0
41	TN	99732
42	TX	36158
43	UT	310
44	VA	14107
45	VT	0
46	WA	0
47	WI	1387
48	WV	4293
49	WY	0
DataSet2

Cell Formulas
Range	Formula
D2	=COUNTIF(B1:B49,"<"&G2)
D3	=COUNTIFS($B$1:$B$49,">"&F3,$B$1:$B$49,"<"&G3)
D4	=COUNTIFS($B$1:$B$49,">"&F4,$B$1:$B$49,"<"&G4)
D5	=COUNTIFS($B$1:$B$49,">"&F5,$B$1:$B$49,"<"&G5)
D6	=COUNTIFS($B$1:$B$49,">"&F6,$B$1:$B$49,"<"&G6)
D7	=COUNTIFS($B$1:$B$49,">"&F7,$B$1:$B$49,"<"&G7)
D8	=COUNTIFS($B$1:$B$49,">"&F8,$B$1:$B$49,"<"&G8)
D9	=COUNTIFS($B$1:$B$49,">"&F9,$B$1:$B$49,"<"&G9)
D10	=COUNTIFS($B$1:$B$49,">"&F10,$B$1:$B$49,"<"&G10)
D11	=COUNTIF($B$1:$B$49,">"&F11)

 

[Comfy]

I think this works, 49 values in total, 40 are unique.

Excel 2010

	A	B	C	D	E
1	AL	43601		Bins	Distribution
2	AR	564			10
3	AZ	50		0	3
4	CA	2677		24	2
5	CO	598		144	4
6	CT	231		320	3
7	DC	20		1000	4
8	DE	0		1704	7
9	FL	57137		5516	0
10	GA	12862		7104	2
11	IA	197		8316	14
12	ID	0
13	IL	8306
14	IN	10049
15	KS	222
16	KY	28491
17	LA	2184
18	MA	9925
19	MD	1780
20	ME	3
21	MI	25703
22	MN	0
23	MO	3551
24	MS	2113
25	MT	0
26	NC	24093
27	ND	0
28	NE	1564
29	NH	346
30	NJ	2257
31	NM	7977
32	NV	71
33	NY	0
34	OH	10182
35	OK	1584
36	OR	1545
37	PA	19525
38	RI	12
39	SC	14785
40	SD	0
41	TN	99732
42	TX	36158
43	UT	310
44	VA	14107
45	VT	0
46	WA	0
47	WI	1387
48	WV	4293
49	WY	0

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D3 {=SMALL(IF(FREQUENCY($B$1:$B$49,$B$1:$B$49)>0,$B$1:$B$49),ROWS($1:1))*(SUM(IF(FREQUENCY($B$1:$B$48,$B$1:$B$49)>0,1))/10)*ROW(1:1)} E2:E11 {=FREQUENCY($B$1:$B$49,$D$3:$D$11)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The general idea is this:

Count the unique values, I think 40 in this example.
Divide by the number of desired distributions, 10 in this example.
This will give us 4, we can then use a nth highest value using multiple of 4 so

4th highest, 8th highest, 12th highest etc.......

These 10 values then feed the Distribution count.


......Well, that's the theory anyway.

 

[Comfy]

This isn't working as intended under scrutiny. Needs some work.

 

[Comfy]

I think this is alittle better although I'm not convinced.

Excel 2010
	A	B	C	D	E
2	AL	43601		Bins	Distribution
3	AR	564			14
4	AZ	50		50	4
5	CA	2677		231	4
6	CO	598		598	4
7	CT	231		1584	4
8	DC	20		2257	4
9	DE	0		7977	4
10	FL	57137		10182	4
11	GA	12862		19525	4
12	IA	197		36158	3
Sheet1

Cell Formulas
Range	Formula
D4	=SMALL($B$2:$B$50,COUNTIF($B$2:$B$50,MIN($B$2:$B$50))+(SUM(IF(FREQUENCY($B$2:$B$49,$B$2:$B$50)>0,1))/10)*ROW(1:1))
E3:E12	{=FREQUENCY($B$2:$B$50,$D$4:$D$12)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Comfy]

Less than 50 is so high comparatively due to the number of zeros.

 

[mrmmickle1]

Comfy,

Sorry, for the delayed response I was writing a macro for my co-worker.

Thanks so much!! The last formula set looks like it will work for my needs!! This has saved me a huge amount of time. Without your help I was going to have to manually fiddle with the bins 39 times then make a grid of all of the bins so I could index the appropriate bin distribution for each of 39 different scenarios. Your Awesome!

 

[Comfy]

Comfy,

Sorry, for the delayed response I was writing a macro for my co-worker.

Thanks so much!! The last formula set looks like it will work for my needs!! This has saved me a huge amount of time. Without your help I was going to have to manually fiddle with the bins 39 times then make a grid of all of the bins so I could index the appropriate bin distribution for each of 39 different scenarios. Your Awesome!

You're Welcome.