Hi all,

Does Excel 2003 have CDF feature in its histogram plotting function?

Here CDF means "Cumulative Probability Density Function",

We know that histogram is actually discretized version of PDF, which stands
for "Probability Density Function".

But where can I find the CDF plotting function in Excel?

Thanks a lot

cfman

I think you will have to write a function to integrate the pdf
yourself.

Within a spreadsheet you can use the FREQUENCY() function on very
small partitions/bins to create the data via which you chart the pdf,
then doing a cumulative sum of the results will give a good
approximation of the cdf...works best on a large data set.

Within VBA I use the following functions to create a 2-d array of
breaks and frequencies...you could add a 3rd dimension to keep a
running total of the frequencies and chart that for the cdf.


Function Hist2(Breaks As Long, arr() As Double, Optional
FreqAsPercentage As Boolean = False)

'calculates bins and frequencies for an array
' where Breaks = no. of breaks
'have to use the GetMax and GetMin functions as arr is likely to have
more than 65,535 elements

Dim i As Long, j As Long, ArrSize As Double
Dim length As Double
Dim MaxValue As Double
Dim MinValue As Double
ReDim breaksNfreq(1 To Breaks, 1 To 2) As Double 'first column is
breaks, second is freq

'Assign initial value for the frequency array
For i = 1 To Breaks
breaksNfreq(i, 2) = 0
Next i

MaxValue = GetMax(arr)
MinValue = GetMin(arr)

'Linear interpolation
length = (MaxValue - MinValue) / Breaks
For i = 1 To Breaks
breaksNfreq(i, 1) = MinValue + length * i
Next i

'Counting the number of occurrences for each of the bins
For i = LBound(arr) To UBound(arr)
If (arr(i) <= breaksNfreq(1, 1)) Then breaksNfreq(1, 2) =
breaksNfreq(1, 2) + 1
If (arr(i) >= breaksNfreq(Breaks - 1, 1)) Then
breaksNfreq(Breaks, 2) = breaksNfreq(Breaks, 2) + 1
For j = 2 To Breaks - 1
If (arr(i) > breaksNfreq(j - 1, 1) And arr(i) <=
breaksNfreq(j, 1)) Then
breaksNfreq(j, 2) = breaksNfreq(j, 2) + 1
Exit For
End If
Next j
Next i

If FreqAsPercentage = True Then
ArrSize = (UBound(arr) - LBound(arr) + 1)
For i = 1 To UBound(breaksNfreq)
breaksNfreq(i, 2) = breaksNfreq(i, 2) / ArrSize
Next i
End If
Hist2 = breaksNfreq()
End Function

Function GetMax(arr() As Double) As Double
Dim i As Long
Dim j As Long
Dim z As Long

i = LBound(arr)
j = UBound(arr)

GetMax = arr(i)
For z = i + 1 To j
If arr(z) > GetMax Then GetMax = arr(z)
Next z
End Function

Function GetMin(arr() As Double) As Double
Dim i As Long
Dim j As Long
Dim z As Long

i = LBound(arr)
j = UBound(arr)

GetMin = arr(i)
For z = i + 1 To j
If arr(z) < GetMin Then GetMin = arr(z)
Next z


Hope that helps
Lloyd

cfman -

The Histogram in Excel's Analysis ToolPak has a checkbox for "Cumulative
Percentage."

You find it at Tools | Data Analysis | Histogram.

(For an alternative, see my free Better Histogram add-in at
www.treeplan.com.)

In general, a histogram is usually a column chart of a frequency
distribution, where the frequency distribution is summarizing real data.

For me, "CDF" usually means "Cumulative Distribution Function."

- Mike
http://www.mikemiddleton.com

"cfman" <(E-Mail Removed)> wrote in message
news:%23FhnUc$(E-Mail Removed)...
> Hi all,
>
> Does Excel 2003 have CDF feature in its histogram plotting function?
>
> Here CDF means "Cumulative Probability Density Function",
>
> We know that histogram is actually discretized version of PDF, which
> stands for "Probability Density Function".
>
> But where can I find the CDF plotting function in Excel?
>
> Thanks a lot

see:

http://groups.google.com/group/micro...be32b7dc60cde8
--
Gary''s Student
gsnu200712