Statistics in Excel HELP!

[barnetod]

Hi everyone, first thanks for the help.

I am trying to create a histogram and let me explain some of the data.

I have a list of data separated like so:
3.5
3.6
3.8
3.8
3.9
3.9
etc...

Now I need to calculate the frequencies of these like so:
3.5 (up to but not including) 4.0
4.0 (up to but not including) 4.5

I can count out the frequencies myself but i need to create a histogram (bar graph) with the y axis as the frequency and the x axis as the IMR(infant mortality rate: ie the 3.5 (up to but not including) 4.0).

I am having problems with calculating the frequencies because when setting the bin values because they include 4.0.

If this is confusing I can provide some more information, but cannot find a good tutorial that would create a histogram anywhere remotely close to the histogram i drew by hand.

[Bryan Hessey]

Hi everyone, first thanks for the help.

I am trying to create a histogram and let me explain some of the data.

I have a list of data separated like so:
3.5
3.6
3.8
3.8
3.9
3.9
etc...

Now I need to calculate the frequencies of these like so:
3.5 (up to but not including) 4.0
4.0 (up to but not including) 4.5

I can count out the frequencies myself but i need to create a histogram (bar graph) with the y axis as the frequency and the x axis as the IMR(infant mortality rate: ie the 3.5 (up to but not including) 4.0).

I am having problems with calculating the frequencies because when setting the bin values because they include 4.0.

If this is confusing I can provide some more information, but cannot find a good tutorial that would create a histogram anywhere remotely close to the histogram i drew by hand.

Select a column to hold the frequecies range, ie, in column B put 4.0, 4.5 5.0 etc and in the next column,
if your data starts on row 1, then in C1 put

=SUM(IF(A$1:A$19<B1,1,0))

CSE
and in C2 put

=SUM(IF(A$1:A$19<B2,1,0))-SUM(C$1:C1)

CSE (CTRL/Shift/Enter) and formula fill to the end of your frequency range.

this will count your frequencies.

note, then just a Bar chart on columns B & C with B as your Category X axis labels.

Does this help?
---

[robert111]

create a separate table like this

0....... .5
.5.......1.0
1.0......1.5
1.5......2.0

etc, call it table

Now in a spare column next to your data in column A

=vlookup(A1,table,2)

this will give you your bin numbers

Now create a pivot table to count how many in each bin, and use this to draw the histogram.
note that 0.499999 will return 0.5
0.500000 will return 1.0

if you wanted to include 0.5 in the 0 / 0.5 group, you would make the left hand column of table read

0.0
0.50000000001
1.00000000001
1.50000000001

etc