calculate pdf (probability density function) in Excel

adp477
adp477 used Ask the Experts™
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
Thanks sebastienm.

How do i create an array of uniques values.

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

Thanks.

Author

Commented:
Thanks sebastienm.

How do i create an array of uniques values.

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

Thanks.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Author

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.
Yes, but with this method, if you change a value in
your array, it will not update automatically, while the
array formula will.

Sebastien

Author

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.

Author

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.

Author

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.

Author

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.
adp477,
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
adp477,

=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
 

Author

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.
adp477,

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.
Hi adp477,
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

Please leave any comments here within the next seven days.
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
Per recommendation

RotaredoM
CS Mod @ EE

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial