Categories

[SnIPpY]

Ok. So I have excel 2007, and I have been playing around for a while now, And I just cannot figure out how to make categories in data.

My data, for e.g. goes like this:
1.1
1.2
1.3
........
9.7
9.8
9.9
10.0

Now how do I make categories for instance of every 0.5. So classes of 1.1 - 1.5 ;1.6-2.0 ; 2.1 - 2.5;2.6 - 3.0 etc.

Bear in mind my data set is huge so doing it manually will take me ages.

Thanx

 

[GlennUK]

Before anything else, can you explain why you want the categories? What will you be doing with them?

 

[SnIPpY]

I will be making a graph of it, so I will also need the amount of each number there is per category.

There is for example more than one 1.1 or 9.3.

So a bar graph with the amount for each number in categories is for now the aimed outcome

 

[jim may]

Sample below; guessing at a use...

Excel Workbook
	A	B	C	D	E	F
1	1.1			Categories
2	1.2			Min	Max	Count
3	1.3			1.1	1.5	5
4	1.4			1.6	2	5
5	1.5			2.1	2.5	5
6	1.6			2.6	3	5
7	1.7			3.1	3.5	5
8	1.8			3.6	4	5
9	1.9
10	2
11	2.1
12	2.2
13	2.3
14	2.4
15	2.5
16	2.6
17	2.7
18	2.8
19	2.9
20	3
21	3.1
22	3.2
23	3.3
24	3.4
25	3.5
26	3.6
27	3.7
28	3.8
29	3.9
30	4
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D3	=A1
E3	=D3+0.4
F3	=COUNTIF(A:A,">="&D3)-COUNTIF(A:A,">="&E3)+1

 

[SnIPpY]

That would only partially solve my problem.

D3 would equal A1 yes, but D4 will not. It will be D6. And I want to just have a formula or function which places everything into a category and counts it.

I have tried countif and countifs and if functions and the problem is that I use quite a lot of categories (45) so it takes one hell of a time.

 

[jim may]

Cell D4 has the formula =E3+0.1 (Which the HTML Maker failed to show). Anyway,
It was just a guess at how to begin to acheive something for you...
Good Luck,
Jim

 

[MrVillareal]

Hi,

Is this what you want?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">0.1</td><td style="text-align: right;background-color: #FFFF00;;">0.4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Data</td><td style=";">Categories</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Lower</td><td style=";">Upper</td><td style=";">Categories</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1.1</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1.1</td><td style="text-align: right;;">1.5</td><td style=";">1.1-1.5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1.2</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1.6</td><td style="text-align: right;;">2</td><td style=";">1.6-2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1.3</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2.1</td><td style="text-align: right;;">2.5</td><td style=";">2.1-2.5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1.4</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2.6</td><td style="text-align: right;;">3</td><td style=";">2.6-3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1.5</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3.1</td><td style="text-align: right;;">3.5</td><td style=";">3.1-3.5</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1.6</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3.6</td><td style="text-align: right;;">4</td><td style=";">3.6-4</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1.7</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4.1</td><td style="text-align: right;;">4.5</td><td style=";">4.1-4.5</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">1.8</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4.6</td><td style="text-align: right;;">5</td><td style=";">4.6-5</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1.9</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5.1</td><td style="text-align: right;;">5.5</td><td style=";">5.1-5.5</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">2</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5.6</td><td style="text-align: right;;">6</td><td style=";">5.6-6</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2.1</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6.1</td><td style="text-align: right;;">6.5</td><td style=";">6.1-6.5</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">2.2</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6.6</td><td style="text-align: right;;">7</td><td style=";">6.6-7</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">2.3</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7.1</td><td style="text-align: right;;">7.5</td><td style=";">7.1-7.5</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2.4</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7.6</td><td style="text-align: right;;">8</td><td style=";">7.6-8</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">2.5</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8.1</td><td style="text-align: right;;">8.5</td><td style=";">8.1-8.5</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">2.6</td><td style=";">2.6-3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8.6</td><td style="text-align: right;;">9</td><td style=";">8.6-9</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">2.7</td><td style=";">2.6-3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9.1</td><td style="text-align: right;;">9.5</td><td style=";">9.1-9.5</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">2.8</td><td style=";">2.6-3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9.6</td><td style="text-align: right;;">10</td><td style=";">9.6-10</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet21</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F3</th><td style="text-align:left">=A3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=F3+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H3</th><td style="text-align:left">=F3&"-"&G3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F4</th><td style="text-align:left">=G3+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G4</th><td style="text-align:left">=F4+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H4</th><td style="text-align:left">=F4&"-"&G4</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F5</th><td style="text-align:left">=G4+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G5</th><td style="text-align:left">=F5+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H5</th><td style="text-align:left">=F5&"-"&G5</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F6</th><td style="text-align:left">=G5+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G6</th><td style="text-align:left">=F6+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H6</th><td style="text-align:left">=F6&"-"&G6</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F7</th><td style="text-align:left">=G6+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G7</th><td style="text-align:left">=F7+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H7</th><td style="text-align:left">=F7&"-"&G7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F8</th><td style="text-align:left">=G7+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G8</th><td style="text-align:left">=F8+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H8</th><td style="text-align:left">=F8&"-"&G8</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F9</th><td style="text-align:left">=G8+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G9</th><td style="text-align:left">=F9+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H9</th><td style="text-align:left">=F9&"-"&G9</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F10</th><td style="text-align:left">=G9+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G10</th><td style="text-align:left">=F10+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H10</th><td style="text-align:left">=F10&"-"&G10</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F11</th><td style="text-align:left">=G10+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G11</th><td style="text-align:left">=F11+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H11</th><td style="text-align:left">=F11&"-"&G11</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F12</th><td style="text-align:left">=G11+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G12</th><td style="text-align:left">=F12+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H12</th><td style="text-align:left">=F12&"-"&G12</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F13</th><td style="text-align:left">=G12+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G13</th><td style="text-align:left">=F13+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H13</th><td style="text-align:left">=F13&"-"&G13</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F14</th><td style="text-align:left">=G13+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G14</th><td style="text-align:left">=F14+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H14</th><td style="text-align:left">=F14&"-"&G14</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F15</th><td style="text-align:left">=G14+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G15</th><td style="text-align:left">=F15+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H15</th><td style="text-align:left">=F15&"-"&G15</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F16</th><td style="text-align:left">=G15+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G16</th><td style="text-align:left">=F16+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H16</th><td style="text-align:left">=F16&"-"&G16</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F17</th><td style="text-align:left">=G16+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G17</th><td style="text-align:left">=F17+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H17</th><td style="text-align:left">=F17&"-"&G17</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F18</th><td style="text-align:left">=G17+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G18</th><td style="text-align:left">=F18+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H18</th><td style="text-align:left">=F18&"-"&G18</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F19</th><td style="text-align:left">=G18+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G19</th><td style="text-align:left">=F19+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H19</th><td style="text-align:left">=F19&"-"&G19</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F20</th><td style="text-align:left">=G19+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G20</th><td style="text-align:left">=F20+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H20</th><td style="text-align:left">=F20&"-"&G20</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A4,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A6,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B7</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A7,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A8,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A10,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B11</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A11,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B12</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A12,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B13</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A13,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B14</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A14,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B15</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A15,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B16</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A16,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B17</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A17,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B18</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A18,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B19</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A19,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B20</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A20,$F$3:$H$20,3,TRUE</font>)</td></tr></tbody></table></td></tr></table><br />

 

[SnIPpY]

Very close to yes, but typing the categories next to each number will take just as long as actually counting everything in each category

 

[MrVillareal]

Im not typing any categories. I used formulas. how about this?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">0.1</td><td style="text-align: right;background-color: #FFFF00;;">0.4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Data</td><td style=";">Categories</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Lower</td><td style=";">Upper</td><td style=";">Categories</td><td style=";">Count</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1.1</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1.1</td><td style="text-align: right;;">1.5</td><td style=";">1.1-1.5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1.2</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1.6</td><td style="text-align: right;;">2</td><td style=";">1.6-2</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1.3</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2.1</td><td style="text-align: right;;">2.5</td><td style=";">2.1-2.5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1.4</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2.6</td><td style="text-align: right;;">3</td><td style=";">2.6-3</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1.5</td><td style=";">1.1-1.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3.1</td><td style="text-align: right;;">3.5</td><td style=";">3.1-3.5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1.6</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3.6</td><td style="text-align: right;;">4</td><td style=";">3.6-4</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1.7</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4.1</td><td style="text-align: right;;">4.5</td><td style=";">4.1-4.5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">1.8</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4.6</td><td style="text-align: right;;">5</td><td style=";">4.6-5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">1.9</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5.1</td><td style="text-align: right;;">5.5</td><td style=";">5.1-5.5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">2</td><td style=";">1.6-2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5.6</td><td style="text-align: right;;">6</td><td style=";">5.6-6</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2.1</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6.1</td><td style="text-align: right;;">6.5</td><td style=";">6.1-6.5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">2.2</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6.6</td><td style="text-align: right;;">7</td><td style=";">6.6-7</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">2.3</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7.1</td><td style="text-align: right;;">7.5</td><td style=";">7.1-7.5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2.4</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">7.6</td><td style="text-align: right;;">8</td><td style=";">7.6-8</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">2.5</td><td style=";">2.1-2.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8.1</td><td style="text-align: right;;">8.5</td><td style=";">8.1-8.5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">2.6</td><td style=";">2.6-3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8.6</td><td style="text-align: right;;">9</td><td style=";">8.6-9</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">2.7</td><td style=";">2.6-3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9.1</td><td style="text-align: right;;">9.5</td><td style=";">9.1-9.5</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">2.8</td><td style=";">2.6-3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9.6</td><td style="text-align: right;;">10</td><td style=";">9.6-10</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet21</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F3</th><td style="text-align:left">=A3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=F3+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H3</th><td style="text-align:left">=F3&"-"&G3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F4</th><td style="text-align:left">=G3+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G4</th><td style="text-align:left">=F4+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H4</th><td style="text-align:left">=F4&"-"&G4</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F5</th><td style="text-align:left">=G4+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G5</th><td style="text-align:left">=F5+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H5</th><td style="text-align:left">=F5&"-"&G5</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H5</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F6</th><td style="text-align:left">=G5+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G6</th><td style="text-align:left">=F6+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H6</th><td style="text-align:left">=F6&"-"&G6</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I6</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F7</th><td style="text-align:left">=G6+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G7</th><td style="text-align:left">=F7+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H7</th><td style="text-align:left">=F7&"-"&G7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I7</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H7</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F8</th><td style="text-align:left">=G7+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G8</th><td style="text-align:left">=F8+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H8</th><td style="text-align:left">=F8&"-"&G8</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I8</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H8</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F9</th><td style="text-align:left">=G8+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G9</th><td style="text-align:left">=F9+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H9</th><td style="text-align:left">=F9&"-"&G9</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I9</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H9</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F10</th><td style="text-align:left">=G9+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G10</th><td style="text-align:left">=F10+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H10</th><td style="text-align:left">=F10&"-"&G10</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I10</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H10</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F11</th><td style="text-align:left">=G10+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G11</th><td style="text-align:left">=F11+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H11</th><td style="text-align:left">=F11&"-"&G11</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I11</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H11</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F12</th><td style="text-align:left">=G11+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G12</th><td style="text-align:left">=F12+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H12</th><td style="text-align:left">=F12&"-"&G12</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I12</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H12</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F13</th><td style="text-align:left">=G12+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G13</th><td style="text-align:left">=F13+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H13</th><td style="text-align:left">=F13&"-"&G13</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I13</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H13</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F14</th><td style="text-align:left">=G13+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G14</th><td style="text-align:left">=F14+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H14</th><td style="text-align:left">=F14&"-"&G14</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I14</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H14</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F15</th><td style="text-align:left">=G14+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G15</th><td style="text-align:left">=F15+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H15</th><td style="text-align:left">=F15&"-"&G15</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I15</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H15</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F16</th><td style="text-align:left">=G15+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G16</th><td style="text-align:left">=F16+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H16</th><td style="text-align:left">=F16&"-"&G16</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I16</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H16</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F17</th><td style="text-align:left">=G16+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G17</th><td style="text-align:left">=F17+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H17</th><td style="text-align:left">=F17&"-"&G17</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I17</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H17</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F18</th><td style="text-align:left">=G17+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G18</th><td style="text-align:left">=F18+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H18</th><td style="text-align:left">=F18&"-"&G18</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I18</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H18</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F19</th><td style="text-align:left">=G18+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G19</th><td style="text-align:left">=F19+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H19</th><td style="text-align:left">=F19&"-"&G19</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I19</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H19</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F20</th><td style="text-align:left">=G19+$F$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G20</th><td style="text-align:left">=F20+$G$1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">H20</th><td style="text-align:left">=F20&"-"&G20</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I20</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$3:$B$20,H20</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A3,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A4,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A5,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A6,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B7</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A7,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B8</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A8,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A10,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B11</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A11,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B12</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A12,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B13</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A13,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B14</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A14,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B15</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A15,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B16</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A16,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B17</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A17,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B18</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A18,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B19</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A19,$F$3:$H$20,3,TRUE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B20</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A20,$F$3:$H$20,3,TRUE</font>)</td></tr></tbody></table></td></tr></table><br />

 

[SnIPpY]

Not exactly, but it gave me an idea which should work.

I'll still have to count each category buy myself though