# Beta distribution from mean and quantile

Suppose I'm given the mean and one quantile (e.g. the 20% quantile) of a random variable $x$, and I want to find the parameters $\alpha$ and $\beta$ of a Beta distribution that has the same mean and quantile. Is there an efficient way to do it?

Using just the mean, I know that since $\bar{x} = \frac{\alpha}{\alpha+\beta}$, we have $\beta = \frac{\alpha}{\bar{x}} - \alpha$. So we only really have one parameter to estimate. But I'm unsure how to use the quantile information to take the next step. Maybe there's something I can do with the Incomplete Beta when I know the ratio $\frac{\beta}{\alpha} = \frac{1-\bar{x}}{\bar{x}}$?

I have access to R myself, so I could use a numerical optimizer for this, but ideally I need a method that can be carried out in Excel in someone else's environment. Excel does have BETA.DIST() and BETA.INV() functions available. A look-up table would be fine, but a closed-form formula would be better if it's possible.

If you really have to do it with pesky Excel:

1. Create cells with quantile probability $p$, quantile value $q$, mean $m$.

2. Create a cell with some initial $\alpha$ value. Create a cell with formula $\beta=\left(\frac{1-m}{m}\right)\alpha$.

3. Create a cell with formula $\mathrm{abs}(q - \mathrm{beta.inv}(p, \alpha,\beta))$.

4. Go to "Data" > "What-If Analysis" > "Goal Seek". Choose the previous cell for item "Set cell", put $0$ in "To value", and choose the $\alpha$ cell for "By changing cell". Press "OK".

5. Next time: Use R! (I'm joking. I know you're an R user.)

• There's a built-in Excel solver? That I did not know. I'll have to look at that, thanks. – Ken Williams Oct 2 '12 at 21:46
• I would of course use R myself - this is work for someone else who already has an Excel-based "workflow" they want to slot a little analysis step into. – Ken Williams Oct 2 '12 at 22:24
• Ken: I understand your suffering. Once I had to translate the Fortran code of R's built in loess regression function to VB. You can imagine how much that sucks. – Zen Oct 2 '12 at 22:27
• +1 Note that (1) not all combinations of $(p,q,m)$ are even possible (even when individually their values make sense for Beta distributions) and (2) in Excel, solutions may be imprecise. – whuber Oct 2 '12 at 22:31