Right now, I have a list of 100 firms and the number of employees in each firm.

I am trying to get excel to tell me how many firms employee 0-10 workers, 10 - 20 workers, and so on.

How do I get excel to do this type of counting operation for me.

1/24/2006 7:30:41 AM

You want a histogram.
create a list of "bin values", or how you want to separate the values:

e.g.
0
10
20
30
40

then use the "=frequency(data, bins)", which takes as its first argument the 'data array' of the actual numbers, and it's second argument the 'bin array', which is the list 'bin values' that you just created.

To make it work, highlight all the cells just to the right of the 'bin values' list, but make the top cell "active" (i.e. it will be white, and the other cells will be normal highlight color). Then type the formula in (it will show up in the first, active cell), but after you complete the formula, press "shift + control + Enter", which will fill the formula down the whole highlighted list creating an array-formula.

1/24/2006 8:29:15 AM

did you get that to work or what?
=frequency() and =vlookup() are two of the most useful and time-saving excel formulas that most people aren't aware of.
Then array-formulas and pivot-tables are also two of the most underused and powerful excel features, mostly because they are hard to discover, and confusing to use even when you're aware of them.

1/25/2006 4:05:42 AM

i never figured out pivot-tables

1/25/2006 4:12:32 AM

yeah, it still takes me lots of experimenting to make a pivot-table do exactly what I want. But once you get it set up right, the amount of data you can extract and the reports it can create are incredible.

If you don't feel like diving into Pivot-Tables now and don't mind waiting a year or so, you can start using them in Office 12 when it is released. I've been planning on making a thread about Office 12 because it is a complete overhaul of the Office interface and lots of functionality. One big improvement for Excel is a completely new pivot-table engine. Check out the Pivot-Table section of the Excel 12 development blog here (start at the bottom entry and work your way up) - http://blogs.msdn.com/excel/archive/category/11544.aspx

Also being overhauled is Conditional Formatting, which is again one of the most underused and useful features in excel. Conditional formatting now though can be cryptic if you want to use formulas, and quite limited in it's functionality and number of options. But in Excel 12 there is are big changes -
http://blogs.msdn.com/excel/archive/category/11358.aspx
Especially cool is the ability to assign a color gradient to a series of cells. Conditional formatting can automatically divide the color gradient evenly between the min/max values of the cells and shade each cell accordingly. Or, CF can automatically insert 'data bars' as backgrounds in each cell - a kind of bar graph graphically showing the magnitude of the value in the current cell.

Be sure to check out the Office 12 Interface Blog, which details the interface changes (hint - there is no more menu bar - it's replaced by 'the Ribbon') http://blogs.msdn.com/jensenh/archive/category/10923.aspx

1/25/2006 4:34:12 AM

Or there's the poor man's way of doing it with COUNTIF


Cell B1: less than 10 employees =countif(A1:A100,"<10")
Cell B2: 10-20 employess =countif(A1:A100,"<20") - B1
Cell B3: 20-30 employess =countif(A1:A100,"<30") - B1 - B2

1/25/2006 1:49:35 PM

^ if you want to use that hack method, you can still do it using one (well 2) formulas, instead of hard coding in stuff like "<10", "<20", and '- B1', '- B1 - B2', etc.

Assuming you have all your data in A1:A100
in B1:B10 make your list of bins, 10, 20, 30 (hint - in B1 type 10 and in B2 type 20. Then highlight B1:B2 and mouse-over the little square in the lower right corner of the highlighted cells - the fill-down/across square, then drag down to B10. Excel will pick up on the pattern (+10) and fill in the rest of the cells for you)

Now in column C we'll make a cumulative list of company sizes corresponding to the cells in column B. That is, C3 will show the number of companies with <30, C9 will show all companies with <90, etc.
C1: =countif($A$1:$A:$100,"<"&$B1)
and fill that formula down to C10. That way you have one formula that takes the data from column B to get the size of the company.

Now in column D we will do the list that you want with all companies between two values. Copy the formula in C1 exactly to D1 and D2.
Now modify D2 so that it subtracts the value in C1.
D2: =COUNTIF($A$1:$A$100,"<"&$B2)-C1
Now fill that formula down to D10.
D10 will look like =COUNTIF($A$1:$A$100,"<"&$B10)-C9

Column D will now have the exact same data as with using the =frequency operator. You can hide column C now if you don't want to see that data.
Now that nothign is hardcoded, you can feel free to move stuff around, or change your bin number or something like that, and everythign will update automagically.

1/25/2006 3:07:37 PM

Quote :

"You want a histogram. create a list of "bin values", or how you want to separate the values: e.g. 0 10 20 30 40 "

Tools...data analysis...histogram. Pretty much just tell it what it wants to know from there, or you can do like agentlion said earlier. Might have to go to add-in's and add some stuff first before you'll have the data analysis option, I don't remember

1/25/2006 7:00:44 PM