Need help

  • Thread starter Thread starter siva
  • Start date Start date
S

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
 
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.
Click to expand...

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 D1:D4 (yes, D4), type the array formula[*]
=FREQUENCY(B1:B100,C1:C3) (yes, C3), and press ctrl+shift+Enter.

D1:D4 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 D1:D4 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. D1:D4), press F2 and edit, then press ctrl+shift+Enter.
Caveat: It is difficult to change the number of cells in a multicell
array.
 
Back
Top