# Ogive Curve

M

#### MR P

I have a set of data and wanting to create an Ogive Curve

71 77 68 64 55 50 45 40 35
31 33 36 40 45 50 55 63 70
72 74 66 63 61 60 56 50 46
41 38 34 39 41 46 50 56 57
51 48 42 46 51 58 59 52 47
43 44 47 53 48 48 49 50 42

Can you help me do this and the proper way to solve this? I see one response
that sorting the data from lease to greatest value will make an ogive curve.
Is there a mathematical way of doing this? Hoping for your immediate
response.

M

#### Mike Middleton

MR P -
I have a set of data and wanting to create an Ogive Curve
71 77 68 ...
...
Can you help me do this and the proper way to solve this? I see one
response
that sorting the data from lease to greatest value will make an ogive
curve.
Is there a mathematical way of doing this? Hoping for your immediate
response.

What do you mean by "a mathematical way of doing this"?

You asked the original question in an Excel newsgroup, and I provided
step-by-step instructions for creating an ogive curve using Excel. Please
provide more details about what you want.

- Mike

++++++++++++++++++++++++++

A P Me -

An ogive curve is a chart of a cumulative frequency distribution or
cumulative relative frequency distribution.

In Excel, arrange the data in a single column. Sort the data (select the
column and click the AZ-with-the-down-arrow icon or use the Data | Sort
command). In an adjacent column on the right, enter the ranks (1 through 54
for your data, which can be entered by typing the 1 and the 2, selecting
both cells, and double-clicking the fill button in the lower right corner of
the selection or dragging the fill button down). Select the cells containing
the data and the ranks (e.g., the range A1:B54 if that's where the data and
ranks are). Click the Chart Wizard icon (which looks like a vertical bar
column chart), choose the XY (Scatter) chart type, and choose the "Scatter
with data points connected by lines" chart sub-type.

- Mike
www.mikemiddleton.com

++++++++++++++++++++++++++

H

#### Herbert Seidenberg

It looks like the array is the data, not the frequency.
Why not use the Histogram tool from the Analysis Tools
that you so well descibe in your book,
Data Analysis Using Microsoft Excel

M

#### Mike Middleton

Herbert Seidenberg -
It looks like the array is the data, not the frequency. Why not use the
Histogram tool from the Analysis Tools that you so well descibe in your
book, Data Analysis Using Microsoft Excel <

The ogive produced by the Histogram tool uses one point for each bin, so its
precision depends on how many bins you specify. Also, since that chart is a
combination Line and Column chart type, it may be somewhat limited by the
offset placement of labels on the horizontal axis.

The approach I described in a previous message uses an XY (Scatter) chart
with one point for each original data point, so in some sense it is more
precise.

- Mike
www.mikemiddleton.com

H

#### Harlan Grove

Mike Middleton wrote...
MR P -

What do you mean by "a mathematical way of doing this"?
....

Maybe, FWLIW, noting that an ogive is the integral of the histogram.

If the OP's range were named Sample, a graph range could be created
starting in cell A1 using the formulas

A1:
0

B1:
0

A2:
=MIN(Sample)

B2:
=COUNTIF(Sample,"<="&A2)/COUNT(Sample)

A3:
=SMALL(Sample,ROUND(B2*COUNT(Sample)+1,0))

B3:
=COUNTIF(Sample,"<="&A3)/COUNT(Sample)

Fill A3:B3 down until the col A formula returns #NUM!. This approach
will properly handle duplicate values in Sample.

H

#### Herbert Seidenberg

The Histogram tool will produce the same integral curve as the one
Harlan's approach generates if Cumulative Percentage is selected and
Input Range=Sample
Bin Range= a vector from 31 to 77
and the output is formatted as Number