Area under a curve?? (Resolved)

[Foxer]

If I have a distribution of figures :-

Service calls response times

1 hour = 3
2 hours = 5
3 hours = 8
4 hours = 6
5 hours = 4

I need to determine how long it takes to resolve 80% of the service calls.

The figures form a rough bell-curve and from my fading memory of school statistics, is this a question of finding the area under a curve? That is, to define 80% of the area under the graph and the x intercept at that point is my final answer? I guess I'd need to find 80% of the area under the graph starting at the point where x=0.

Or, I could be totally off track.

Any help would be good.

I'm using MS Excel 2003 but can use VBA/code if required.

Thx.

[Crash893]

http://www.mathwords.com/a/area_under_a_curve.htm

[Crash893]

http://www.mrexcel.com/archive/Chart/12378.html

[Rassis]

Considering the response times as a continuous variable (I didn’t quite understand why you gave only times rounded to units) the response is affirmative – yes it is a “question of finding the area under a curve”. But the question is: what curve? The one provided by the numbers? They are such a few…statistically speaking! Because you have such a few observations, the best course of action is to find the probability distribution that fits the empirical data the best. If you use the qui-squared method, for instance, you will find that a Normal function with parameters: mean = 3.2 and standard deviation = 1.1 present a good fit. With these values in hand, it will now be easy to find the value X such that P(x <= X) = 0.8. Using Excel, you may find X = 4.126 hours (=NORMINV(0.8;3.2;1.1). If, on the contrary, you use your plain data, you will get less accuracy and find X = 4.

Hope it helps.

[Foxer]

Got it - Thx.

I worked out the mean and standard deviation using formulas
average and stdev

then from that, I used norminv using 0.80 (80%) to determine my final solution.

[Foxer]

Sorry Rassis - to answer fully :-

For reporting purposes, I categories the values to brackets as above, but to get a proper curve or smooth graph, I had to fall back to the original data. It took me a bit to realise why my data was a bit skewed.

And yes, as you indicated, NormInv was exactly the function I needed, provided I got the mean and std dev from the raw data first.

Was a lot easier than I imagined.

Thanks.

[Rassis]

I am glad that you found your way to the solution but, take into consideration what I said above about replacing the current empirical distribution by a theoretical one that fits the data available the best. I insist because this is a normal procedure in statistics. If you calculate the mean and the standard deviation (SD) from the original data, you find the mean to be 3.12 and the SD equal to 1.24. But you cannot say beforehand that your data is equivalent to a Normal distribution with these parameters. After you have performed a best-of-fit test, then you get more accuracy and confirm that a Normal probability distribution with parameters N(3.2; 1.1) will do the best. From now on, you use this Normal distribution for your decision making process and discard the raw data. In the future, in case that more and more data are gathered, you may keep on performing the best-of-fit test and find each time more accurate values of the mean and the SD.

Regards,