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?

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?

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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

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.

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

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial