6
$\begingroup$

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.

$\endgroup$
6
$\begingroup$

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.)

$\endgroup$
  • 1
    $\begingroup$ There's a built-in Excel solver? That I did not know. I'll have to look at that, thanks. $\endgroup$ – Ken Williams Oct 2 '12 at 21:46
  • 1
    $\begingroup$ 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. $\endgroup$ – Ken Williams Oct 2 '12 at 22:24
  • $\begingroup$ 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. $\endgroup$ – Zen Oct 2 '12 at 22:27
  • 2
    $\begingroup$ +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. $\endgroup$ – whuber Oct 2 '12 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.