# calculate pdf (probability density function) in Excel

on
I have a big array of numbers 1000+. I need to find the probabilty density function pdf.

Specifically, i need to calculate, what is the probability that number lies between a & b in the whole array.

I saw the prob function. But you have to supply it probablity values of all the points in the array. Is there an way to calculate probability of the individual points in the array.

All help is appreciated.
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Discrete/Continuous probability?

If discrete:
Ceate a new array of unique values with their frequencies.
Then new column: for each number, prob=frequency/some of frequencies.
If working on ranges, you can use the FREQUENCY function as a 1st step.

Sébastien

Commented:
Thanks sebastienm.

How do i create an array of uniques values.

i can use the "count" function to get the frequency.

Thanks.

Commented:
Thanks sebastienm.

How do i create an array of uniques values.

i can use the "count" function to get the frequency.

Thanks.

Commented:
Actually the Frequency(RangeOne,RangeOne) function entered
as a Array Formula will return the frequency for the first
occurence of each number, else, if not first occurence,
will return 0.
Are you working within the spreadsheet or in VBA?

check at that page for counting/extracting unique entries.
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

...but the more i think about it, the moe i believe there
must be a funtion from the Analysis Toolpack to do exactly
what you want. A function simiar to Frequency.

Sébastien

Commented:
Thanks sebastienm,

i am working on an Excel worksheet.

I have added all the add-ins. There is no simple pdf function.

i discovered an easier way to create an uniques array.
select the array. select filter, then advanced filter and then check uniques values only. u get a unique array.

thanks for the help.

Commented:
Yes, but with this method, if you change a value in
your array, it will not update automatically, while the
array formula will.

Sebastien

Commented:
Thanks sebastienm,

i am working on an Excel worksheet.

I have added all the add-ins. There is no simple pdf function.

i discovered an easier way to create an uniques array.
select the array. select filter, then advanced filter and then check uniques values only. u get a unique array.

thanks for the help.

Commented:
Hi sebastienm,

I now want to count the occurance of these unique values in the original array.

i get the result for the first record with dcounta function, but i need to replicate the "field" before every record.

frequency gives me intervals.. i want the occurance of each unique element in the original non-unique array.

thanks.

Commented:
Hi sebastienm,

I now want to count the occurance of these unique values in the original array.

i get the result for the first record with dcounta function, but i need to replicate the "field" before every record.

frequency gives me intervals.. i want the occurance of each unique element in the original non-unique array.

thanks.

Commented:
Hi sebastienm,

I now want to count the occurance of these unique values in the original array.

i get the result for the first record with dcounta function, but i need to replicate the "field" before every record.

frequency gives me intervals.. i want the occurance of each unique element in the original non-unique array.

thanks.

Commented:
Please, do not use the Refresh of your browser after posting a question or comment,
use the 'Reload this question' option from the menu on the top-left.
Refresh-ing will re-post your latest comment... yeah, messy... we all
do this at some here. The problem has been here for quite a while, but not
fixed yet by the EE team.

Regards,
Sébastien
Commented:

=countif(\$A\$1:\$A\$100,G1)
: count number of occurence of G1 (unique value) into
\$A\$1:\$A\$1000 (non-unique value array)

Sébastien

Commented:
Hi sebastienm,

I now want to count the occurance of these unique values in the original array.

i get the result for the first record with dcounta function, but i need to replicate the "field" before every record.

frequency gives me intervals.. i want the occurance of each unique element in the original non-unique array.

thanks.

Commented:

Try this PAQ to obtain your unique list and the frequency of the items within it:
http://www.experts-exchange.com/Applications/MS_Office/Q_20394673.html

A search on google came up with the following hit for probability density:
http://www.experts-exchange.com/Miscellaneous/Math_Science/Q_20315555.html
a bit long-winded, but you may find something useful within the thread.

Hope this helps.

Commented:
No comment has been added lately (123 days) and it's time to clean up this Topic Area.
If this question isn't revived and no action is taken I will leave a recommendation in the Cleanup topic area that this question is:

RECOMMENDATION: Award points to sebastienm

This will be finalized by a moderator and he will use those comments to make his final decision.

-- PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER ! --

Paulo
Cleanup Volunteer

Commented:
Per recommendation

RotaredoM
CS Mod @ EE

Do more with