Link to home
Start Free TrialLog in
Avatar of PsimonTech
PsimonTechFlag for Afghanistan

asked on

Probability of attencance to an event using Excel

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?
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Can you post your code?
ASKER CERTIFIED SOLUTION
Avatar of JT92677
JT92677
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
Avatar of PsimonTech

ASKER

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