Is it possible to have Excel calculate a joint density function based on two sets of descriptive statistics generated by the Data Analysis tool? To explain further, suppose I have two means a and b and two standard deviations c and d. I would like to form the normal distributions defined by the sets of parameters {a,c} and {b,d} and use them to form a joint density function, which is just the distribution defined by both of these distributions over two axes.
4
-
Could someone explain why this question is poorly posed? Is it off-topic in some way?– 114Aug 13, 2014 at 17:31
-
Mr.114, what is it that you want? Please explain in lay man language– PrasannaAug 13, 2014 at 17:38
-
A few questions: Are you assuming the two random variables are independent? Do you want Excel to return a function for the density surface? Or do you want Excel to do something like generate points in the joint distribution?– ExcellllAug 13, 2014 at 17:59
-
@Excellll Since I think avoiding that assumption would complicate things a lot I've decided to assume they're independent. The function for the density surface is more what I'm looking for and if I recall correctly I could use the solver from there to get particular points. Does that sound right?– 114Aug 13, 2014 at 18:08
Add a comment
|
1 Answer
If you have two variables that are normally distributed and assumed to be independent, then the joint density function for those two variables is just
J(x,y) = P(x)*P(y)
where P(x) is the probability density function of X at x, and P(y) is the probability density function of Y at y.
So, in Excel, if you want to find J(x,y), the value of the joint density function for x and y, you can use the built-in NORMDIST function to get what you need.
=NORMDIST(x,mean of X,stdev of X,FALSE)*NORMDIST(y,mean of Y,stdev of Y,FALSE)
An example:
The parameters of two normal random variables X and Y are given. The heat map below shows the value of the joint density function at integer values of X and Y from 0 to 10. The formula used to fill the heat map is
=NORMDIST(B$5,$B$2,$B$3,FALSE)*NORMDIST($A6,$C$2,$C$3,FALSE)
