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
AL	Bins	Distribution
AR
AZ
CA
CO
CT
DC
DE
FL
GA
IA
ID
IL
IN
KS
KY
LA
MA
MD
ME
MI
MN
MO
MS
MT
NC
ND
NE
NH
NJ
NM
NV
NY
OH
OK
OR
PA
RI
SC
SD
TN
TX
UT
VA
VT
WA
WI
WV
WY

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]43601[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]564[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2677[/TD]
[TD="align: right"][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]598[/TD]
[TD="align: right"][/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]231[/TD]
[TD="align: right"][/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1704[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]57137[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5516[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]12862[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7104[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]197[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8316[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]8306[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]10049[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]222[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]28491[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]2184[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]9925[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]1780[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]25703[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]3551[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]2113[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]24093[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]1564[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]346[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]2257[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]7977[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]71[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]10182[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]1584[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]1545[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]

[TD="align: right"]19525[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]14785[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]

[TD="align: right"]99732[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]

[TD="align: right"]36158[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]

[TD="align: right"]310[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]

[TD="align: right"]14107[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]46[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]

[TD="align: right"]1387[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]48[/TD]

[TD="align: right"]4293[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]49[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]{=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)}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2:E11[/TH]
[TD="align: left"]{=FREQUENCY($B$1:$B$49,$D$3:$D$11)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


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.