Beta.dist in excel

I've been trying to understand how the Beta.dist function works in MS Excel. The MS Office help says that Beta.dist returns the beta cumulative distribution function. The FALSE of the function gives the probability density function while the TRUE give the cumulative distribution function.

However, am getting the the value of the PDF using this function to be 101.85% and 12741.07% considering x to be 49% and 50% respectively. So, the area under the PDF > 100% which seems absurd. My alpha = 16995.922 and beta = 17095.415.

When I take the CDF using the function am getting 0.08% and 70.50% for 49% and 50% respectively. If I calculate the PDF at 50%, it comes to 70.42% and the area under my curve sums upto 100%.

sudeshna

• This is not absurd, the PDF may have values $>1$. Dropping your % notation and using a,b for $\alpha, \beta$, I get with my own function beta_pdf(a,b,0.5) = 127.410572 and beta_pdf(a,b,0.49) = 1.018477. For the CDF beta_cdf(a,a,0.49) = 0.000804 and beta_cdf(a,b,0.50) = 0.705008. Commented Sep 11, 2013 at 14:15
• I've tried a few numbers in Excel and get sudeshna's answers. Nothing prevents a pdf from being above one, however, since it is a density. I'd say for pdf values, it's probably better not to describe them as percents. Commented Sep 11, 2013 at 14:22

As noted in the comments, the value of a density function is not the percent probability of the event occurring at that point. Remember, that the "height" is that of a line--having no width, so the actual percentage chance for that point is 0—for any and every continuous distribution (unless there is a mass point) the probability of obtaining a specific value is 0. This is the difference between "almost never" and "never". When wanting probabilities for events whose range is continuous, one must always talk about intervals; e.g. greater than $X$, less than $Y$, between $Q$ and $V$, etc.

Counterexample

Here is a very simple counterexample which I hope you will find intuitive. $$f(x) = \begin{cases} 2 - 2x \quad 0 \leq x \leq 1\\ 0 \quad \quad \quad\textrm{otherwise} \end{cases}$$

This is a valid density as: $$\int_0^1 2 - 2x\;dx = 2x - x^2\bigg|_0^1 = 2 - 1 - 0 + 0 = 1$$ However, the value of the function at $x=0$ is $2$, which is much greater than 1.

The probability of an event being in the interval $(x_0, x_1)$ is: $$2x_1 - x_1^2 - (2x_0 - x_0^2) = \left(x_1 - x_0\right)\left(2-\left(x_1+x_0\right)\right)$$ I'm not rigorously proving it now, but the value $\left(x_1 - x_0\right)\left(2-\left(x_1+x_0\right)\right)$ does not appear to exceed $1$ in the interval where $x_0, x_1 \in [0,1]$.

Partial justification

When $(x_1 - x_0)$ is at its maximum of $1$ that means $x_1 = 1, x_0 = 0$ and then $(x_1 + x_0)$ is also $1$ and the value of the probability is $1(2-1) = 1\cdot1=1$ as we would expect as it is the entire defined universe. When $(x_1 + x_0)$ has its maximum of $2$, both values are $1$, so the second half of the equation is 0, so the probability is 0, as we expect as this is a degenerate interval.

Fuller justification

We can go one step further. Let: $$0 \leq x_0 \leq x_1 \leq 1\\ x_1 = 1 - \epsilon\\ x_0 = 1 - \theta\\$$ Now, obviously, $0 \leq \epsilon \leq \theta \leq 1$. Let's translate the probability: \begin{align} P(x_0 \leq X \leq x_1) &= \left(x_1 - x_0\right)\left(2-\left(x_1+x_0\right)\right)\\ &= \left((1-\epsilon) - (1-\theta)\right)\left(2-\left((1-\epsilon)+(1-\theta)\right)\right)\\ &=(\theta - \epsilon)(\theta + \epsilon)\\ &=\theta^2 - \epsilon^2 \end{align} This last value must be less than or equal to $1$ based on the definition of $\theta$ and $\epsilon$ showing that the density may be allowed to exceed the value of $1$ but the probability will not.

This will hold for all valid densities.

Hope that helps.