Probability of attencance to an event using Excel

PsimonTech
PsimonTech used Ask the Experts™
on
Given a probability of each person attending an event, how do you derive the projected attendance?

I have a list of people that are invited to an event.  I have assigned a probability of attendance to each person (from 0 to 1).   My table compares each probability of attendance to the value derived using the Excel random number formula.  If the probability of attending is equal to or greater than the random number, I have an "If" formula that evaluates to "1", otherwise the "If" formula evaluates to "0".

I created visual basic code to recalculate the spreadsheet a set number of times (say 1,000) and to store the total attendance projection of each recalculation of the random number in a column.  I then average the column of results and use this as my projection of attendance.

How do I do this in Excel using formulas instead of visual basic?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you post your code?
Commented:
Just take the average probability of attendance and multiply by the number of potential attendees. Since you are averaging Probabilities over the same number of values as you have potential attendees it's even easier:

If you send out N=100 invitations, and estimate the P(attend) for each from 0 to 1, the estimated attendance will be the average of all the P(attend) times N.

If you have a P(attendance) for every member, Just add up the column of probabilities, that's your expected attendance, since adding them up and dividing by N to get the average P, then multiplying by N for the expected number, just forget the N and take the sum of P.

If you arrive at P(attendance) using a subsample of the population of attendees, then use the average P times the total population of potential attendees to get an expected number of arrivals.

Jeff
Top Expert 2010

Commented:
Jeff nailed it, in my opinion.  His comment gives you the true expected value of attendance.

What you described in your question with the VBA code is a simulation.  If you run the simulation many times, the results will tend to converge on the expected result, but that is not the same thing as determining what the expected value is.

Commented:
Mathew - if he were to run the simulation long enough, it would converge on the same P(attendance). It's nice to get kudos from a multi-million point expert !! THANKS !!!

Not all situations are this straightforward, and simulations can be useful when doing things like waiting line behavior. How many queues are open, how long does it take to service a customer, how long will a customer wait, how many people will enter the door to seek service over the day, how long does the line have to look to discourage a customer and cause the customer to leave.  I've actually done simulations for waiting lines, customer service, consumer behavior, etc., and they clearly have a place and everyone can understand the outcome if they agree with all the premises (who will wait, how long will they wait, when do customers arrive, etc.).

Jeff

Author

Commented:
I took this approach initially but decided to run a simulation just for fun.  When I ran the simulation, the answer was significantly different than the sum of the probabilities and so I wrongly assumed that summing the probabilities was not the solution.  

It turns out that the person who sent me the original spreadsheet had accidently  pasted the results of some of the IF formulas as values.  When I replaced the values with formulas, the simulation confirmed the sum of the probabilities.

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial