2
$\begingroup$

The size of fish in a lake follows a Normal Distribution with mean m = 1 lb 4 oz and standard deviation s = 3 oz . Fish that weigh less than 1 lb 9 oz must be released back into the lake. Bill wants to reach his limit of 5 “keepers.” What is the probability that Bill must catch at least 40 fish in order to reach his limit.

How do you do this on excel?

$\endgroup$
0

1 Answer 1

0
$\begingroup$

First it'll be easier if we convert everything to ounces or pounds. Let's go for ounces so the mean is 20 oz, and the weight limit is 25 oz.

If $X$ is a normal variable with mean 20 oz and standard deviation 3 oz, then for one fish, the probability that it is heavy enough is $P(X > 25)$.

Excel 2010 has a command NORM.DIST which calculates probabilities with normal distributions, but it only gives you probabilities for "less than". The function is NORM.DIST and you input in this order the $x$ value, the mean, the standard deviation, and whether you want the cumulative area. In your case, to get $P(X < 25)$ you type =NORM.DIST(25, 20,3,TRUE). To get $P(X>25)$ you would need to type =1-NORM.DIST(25, 20,3,TRUE), because the NORM.DIST function only gives you the "less than" version, so you need to subtract it from 1 to get the opposite probability.

This is for one fish. I'll call this probability $p$ from now on.

Now we are interested in whether he has to catch 40 fish to get five that are heavy enough. So either he gets to five that are heavy enough in the first 39, he won't have to catch 40. If he only has four that are heavy enough in the first 39, he will have to catch at least 40. So the probability we are looking for is the probability that, in 39 fish, four or less are heavy enough.

Let $Y$ be the number of fish that are heavy enough in 39 fish. Assuming the fish are independent of each other, and there are enough of them not to change the probability $p$ when you catch fish, we can assume Y is a binomial distribution with probability of success $p$ and 39 trials. We want the probability $P(Y \leq 4)$.

Excel has a function for calculating binomial distribution probabilities too. It's BINOM.DIST. You input, the value you are at, the number of trials, the probability of success, and whether you want cumulative or not. Note that you must have your probability written with a "$\leq$" rather than a "$<$" for this to work. So you would type =BINOM.DIST(4, 39, p, TRUE) (where p is the probability you calculated earlier).

Of course, if this was a problem to solve in a class, you would write your solution on paper a little differently to this!

$\endgroup$

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.