How to calculate Mean, Median, Mode and standard deviation in group data?

Hello,

I am trying to create a worksheet capable of calculating the turnaround processing time for emails in a 14 days time frame.

I am wondering if it is possible to create a dynamic excel worksheet which can calculate Mean, Median, Mode and standard deviation within a range of data as Column B listed below changes every forthnightly.

Sample data below:

Column A Column B
Number of days Email
O – 1 600
2 - 3 700
4 - 5 100
6 - 7 50
7 - 14 30

- First row: 600 emails took a day to complete
- Last row: 30 emails took between 7 - 14 days to complete

1. To find the average processing time
2. To find the median processing time in days
3. What is the standard deviation (in days)

Would greatly appreciate if someone could help.

I apologise if I was unable to articulate the whole scenario clearly.

Many Thanks!

The Mean is relatively easy,
It will be easiest to explain what I am doing if I change the arangement of your columns slightly.

Column A = shortest time in the category
Column B = longest time in the category
Column C = number of e-mails in the category

Column D = C*(B+A)/2
i.e. number of e-mails multiplied by the mid-time for the category.

The mean time for e-mails is then the sum of column D divided by the sum of column C. (=2.12 for the example numbers)

I will have to think a little for the other quantities.

Mark.

Thanks alot Mark!

Looking forward to the your replies!

Cheers,
Ingen