IT Policies

Use Excel's POISSON function to predict business volumes

You can use Excel's POISSON function to make operational decisions based on predicted business volumes, such as predicting the probable number of customers that will contact the help desk this year. Here's how.

You can use Excel's POISSON function to make operational decisions based on predicted business volumes. For example, when hiring for the upcoming end-of-year shutdown, you need to know how many customers to expect will be contacting the help desk. Your records show that the mean number of customers that contacted the help desk last year was 11 per hour. You can use this number and the POISSON function to predict the probable number of customers that will contact the help desk this year. Follow these steps:

  1. In cell A1, enter Mean Number Of Customers Per Hour.
  2. In cell B2, enter 11.
  3. In cell A3, enter Customers.
  4. In cell B3, enter Individual Probability.
  5. In cell C3, enter Cumulative Probability.
  6. In A4:A24, enter the numbers from 0 to 20.
  7. In cell B4, enter the following formula: =POISSON($A4,$B$1,False).
  8. Copy the formula in B4 to B5:B24.
  9. In cell C4, enter the following formula: =POISSON($A4,$B$1,True).
  10. Copy the formula in C4 to C5:C24.

The values in Column B indicate the probability that the number of customers entered in the corresponding cell in Column A will contact the help desk during the holiday season. The values in Column C indicate that 0 to the number of customers entered in the corresponding cell in Column A will contact the help desk. In this example, for 85% of the time, you can expect 0-14 customers per hour to contact the help desk. Consequently, if you hire enough employees to adequately cover 14 customers, you should be covered 85% of the time.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

3 comments
bevg
bevg

It's not a formula I need, but I couldn't resist asking: is this formula a FISHING trip? Poisson is the French word for fish; take out one "S" and it becomes poison.

khit
khit

Great tip! The second argument in the formulas in steps 7 and 9 should be $B$2, not $B$1.

nazzim
nazzim

Or, have the value "11" (Mean Number of Customers per hour) posted in field B1 instead of B2.

Editor's Picks