Need help

[siva]

Say for example i have set of values in a row as given below. I need
to define a graph or a pivot which states how many values are less
than 25 and how many are inbetween 26 to 50 and how many are inbetween
51 to 75 and how many are greater than 75.

B

40
27
4
75
54
75
87
9
48


How do i do it? Please help.

Cheers,
Sivaji

 

[Jim Cone]

How far have you gotten?
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(independent review of Special Sort excel add-in)



"siva" <[email protected]>
wrote in message
news:33a9e5e9-e512-440b-8b0e-4570a5dfa7b8@r19g2000prm.googlegroups.com...

 

[joeu2004]

Say for example i have set of values in a row as given
below. I need to define a graph or a pivot which states
how many values are less than 25 and how many are inbetween
26 to 50 and how many are inbetween 51 to 75 and how many
are greater than 75.

Look at the FREQUENCY function.

Put the __upper__ limits of your bins into column C. For example, put
25 into C1, 50 into C2, and 75 into C3.

Then select D14 (yes, D4), type the array formula[*]
=FREQUENCY(B1:B100,C1:C3) (yes, C3), and press ctrl+shift+Enter.

D14 will now have the counts that you require. D4 is the count of
everything over the last limit in C3 (75).

If you want to graph that, select D14 and use the Chart Wizard on
the toolbar. Exactly how to use and what additional cells you need to
select will depend on the chart type that you select.

-----
[*] Enter an array formula by pressing ctrl+shift+Enter instead of
Enter. Excel will display an array formula surrounded by curly braces
in the Formula Bar, i.e. {=formula}. You cannot type the curly braces
yourself. If you make a mistake, select all of the cells in the array
(e.g. D14), press F2 and edit, then press ctrl+shift+Enter.
Caveat: It is difficult to change the number of cells in a multicell
array.