software

I have a large amount of data (60k lines) and want to display a cumulative
frequency of the data. Example... chart starts @ 0,0 and end at $1.00 (x) and
100% (y). It will visually show at any given x value (between 0 and 1.00) the
cumulative % of the time that value (and below) comes up. (.50 and below is
40% of the data).

I have played with histograms, but that just shows the frequency of 1 x
value. I need this to be cumulative for that x value and all values lower
than it.

Any ideas?

Ted, I think you can use the COUNTIF function: =COUNTIF(x,quot;lt;=xquot;) where x is
the x value you want to calc the frequency of occurence.

quot;tedquot; wrote:

gt; I have a large amount of data (60k lines) and want to display a cumulative
gt; frequency of the data. Example... chart starts @ 0,0 and end at $1.00 (x) and
gt; 100% (y). It will visually show at any given x value (between 0 and 1.00) the
gt; cumulative % of the time that value (and below) comes up. (.50 and below is
gt; 40% of the data).
gt;
gt; I have played with histograms, but that just shows the frequency of 1 x
gt; value. I need this to be cumulative for that x value and all values lower
gt; than it.
gt;
gt; Any ideas?

Supposing the data is in column B, with a label in B1 (quot;Valuequot;), sort the
data, and in column A put a label in A1 (quot;Rankquot;) and starting in A2 use the
formula

=(ROW()-1)/COUNT($B:$B)

Since you can't plot more than 30K points in a chart series (and that's an
exercise in point redrawing that I wouldn't wish on anyone, except the
client who inflicted it on me), in two more columns compute a simpler data
set. In D1 type quot;Rankquot; and in D2101 enter =(ROW()-1)/100. In E1 type
quot;Valuequot; and in E2:E101 enter this formula:

=VLOOKUP(D2,$A$2:$B$2000,2)

But enter the whole range, not just the $A$2:$B$2000 I used in my example.
Now select the data in D:E and create an XY chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
PeltierTech.com/
_______

quot;tedquot; gt; wrote in message
...
gt;I have a large amount of data (60k lines) and want to display a cumulative
gt; frequency of the data. Example... chart starts @ 0,0 and end at $1.00 (x)
gt; and
gt; 100% (y). It will visually show at any given x value (between 0 and 1.00)
gt; the
gt; cumulative % of the time that value (and below) comes up. (.50 and below
gt; is
gt; 40% of the data).
gt;
gt; I have played with histograms, but that just shows the frequency of 1 x
gt; value. I need this to be cumulative for that x value and all values lower
gt; than it.
gt;
gt; Any ideas?