Time Weighted average Help

seals · 2011-08-31 09:13:19

Hi experts,

I am looking for some help with a weighted average function. I am trying to work out the average time taken per incident, but I wish to weight or grade the incident types, The data:

Incident Occurences Weight Weighted Value
Incident A 1 45% 0.45
Incident B 72 5% 3.60
Incident C 138 5% 6.90
Incident D 49 5% 2.45
Incident E 11 10% 1.10
Incident F 15 10% 1.50
Incident G 56 10% 5.60
Incident H 5 10% 0.50

My calculations are as follows, obviously 45% of 1 gives 0.45 as a weighted total, all of the weight %'s sum to 100%, all of the weight values sum to 22.10. I am currently dividing the weight total (22.10 by 8) as there are 8 incident types? This gives me 2.76 this is then multiplied by the total number of incidents (347) which gives me a weighted total in hours of 959 hrs.

Am I correct in my assumptions/calculations or am I looking at this the wrong way.

Any help would be greatly appreciated:cool:

Cheers,

Seals

Bob · 2011-08-31 18:59:49

hi seals

You cannot just take the average of the weighted values by adding and dividing by 8. This doesn't take account of the occurences (usually called the frequencies). You are giving equal importance to A which rarely occurs, and C which occurs a lot.

Multiply each weight by its occurence; add up these numbers to get the total weight; then divide by the total occurences. (Don't use 8 at all!)

If I use f for the occurences and w for the weighted values then the formula is

Hope that helps,

Bob

Last edited by Bob (2011-08-31 19:09:06)

seals · 2011-08-31 19:19:59

Hi Bob,

Thanks for that, I understand your formula and the theory thereof. If I divide 22.10 by 347 I get an extraordinarily small answer 0.0637, however, if I divide 347 by 22.10 I get 15.7 which seems more believable I think.

I have one more part to this. When I get my weighted value by the supplied formula I need to correlate that to the amount of hours worked in one year. In this case that amount is 1710. Is it simply a case of multiplying the weighted average answer by the number of occurrences?

Thank you very much for your help thus far.

Seals

Bob · 2011-08-31 19:48:15

hi seals,

Not 22.1.

Let me make up a simpler example to show why not.

Suppose on 999 occasions the weight was 2 and on just 1 occasion it was 100.

If you add together 2 and 100 you get 102. Divide by 2 and you get 51. That's too high to be 'representative' of all those 2s.

If you divide by 1000 you get 0.102 which is too low.

The true average should be close to 2, as most of the values were 2. Because there was one other value and it was higher, I'd expect an answer of just over 2.

Treat the data like a list of 1000 items:

{2,2,2,2,2,2,2,2,2,2,2................................,2,2,100}

To average these you'd have to do:

2+2+2+2+2+2+2+.....+2+2+2+100 and divide by the amount of numbers which is 1000.

So 999 twos = 1998. add on 100 and the total is 2098.

Now divide by 1000

average = 2098/1000 = 2.098

If put the values into a table, shown below.

Notice that my answer is smaller than the highest weight, and larger than the smallest. This has to happen because the 'average' must be representative of the values and must lie between the extreme values.

I have one more part to this. When I get my weighted value by the supplied formula I need to correlate that to the amount of hours worked in one year. In this case that amount is 1710. Is it simply a case of multiplying the weighted average answer by the number of occurrences?

hhhhmmmm. ?? When we get 4.85 (rounded), what does that mean exactly? Is it the number of hours in one typical day? If so, I would think that x 365 might be a better calculation. To be certain, I need to know more about where these data came from and where you want to go with the statistics.

Bob

Last edited by Bob (2011-08-31 19:58:32)

seals · 2011-08-31 19:56:15

Bob,

ok getting clearer now Thankyou for your time and patience

So now we have a weighted average of 4.85 to work out the time correlation is it just a matter of multiplying the 4.85 by the number of occurrences which would be 1682.95? I am taking this to be the amount of hours it would take to (on weighted average) complete the tasks over a year?

Bob · 2011-08-31 19:59:54

Stay on-line.

I was just editing my post when you posted back. Take a look at the edit while I look at your reply.

Bob

seals · 2011-08-31 20:02:46

ok staying on line. fyi the incidents are actually events that have taken place and i am trying to work out how many people it would take to efficiently complete those tasks. On average a worker would spend 8 hrs a day, 38 hours a week for 45 weeks on average per year. this figure is where I get the 1710 from

Last edited by seals (2011-08-31 20:04:42)

Bob · 2011-08-31 20:04:26

Over what time period did you collect data?

And where did the weights come from?

Bob

seals · 2011-08-31 20:05:28

The time period is one year, the weights are based on the amount of time it takes to complete each task (This part will take time to refine etc)

Bob · 2011-08-31 20:14:21

Ok. I think I understand.

So job C has to be done 138 times and it takes 6.9 hours to do it, right?

Which means you don't need an average at all.

1682.60 is the total hours to do all of those jobs.

And you have 8 x 38 x 45 hours available = 13680 hours

That means one worker can do it alone with time to spare?

Must be something I'm missing here.

Bob

seals · 2011-08-31 20:19:08

Yes that is right but job C is a quick job that takes say 1 hour where job B takes on average 3 hours. So you are right we need to find out how long it takes to efficiently complete all the jobs over one year. The average is useful to forecast what would be likely to occur next year.

So we can forecast that the jobs will decrease by say 5% next year we can then with the average forecast how many staff will be required to complete the tasks. So really it is not only historical but future orientated based on outcomes (if that makes sense)

Last edited by seals (2011-08-31 20:20:45)

Bob · 2011-08-31 20:30:25

So have I answered all of your questions now?

I'm still bothered that I seem to have concluded you only need one worker per year?

Maybe that data was collected in just one typical day rather than over a whole year ??

Bob

seals · 2011-08-31 20:36:42

Bob, that data was collected over the year of 2010 in a remote area of Australia. So the numbers are not high. Just for my pure amusement could I use the weighted average to forecast staffing requirements for new year (I would have the forecasted number of jobs) or would it be easier to follow the solution that you have provided? Being that 1682 hours would be required to complete all jobs, which as you have pointed out would leave the person with time to spare (which coincidently is what I was expecting as an outcome)

I think that I may have over thought the issue at the onset?

Bob · 2011-08-31 22:35:36

h seals

I think that I may have over thought the issue at the onset?

Yes, I think you may have.

I think I'd keep the results in a spreadsheet and make space for revised figures after another year.

But have a column for what the jobs actually are, and when they have to be done. It looks like what you need is a part time worker who can do tasks at different times of the year.

I had originally pictured this as a problem where all those jobs were being done on a daily basis and maybe 20 employees. Then it would make sense to look at whether you could optimise the workforce and perhaps 'trim' by a few persons. Also there would be an interesting job of time allocation in order to avoid periods of slack work.

If you're in Australia it must be near to bedtime, so good night, from the UK,

Bob

seals · 2011-08-31 23:15:23

Bob,

Thank you so much for your time and your thoughts. It is always good to bounce your ideas about the place as it makes you re-evaluate the original problem. This is coloquially know as the KISS method (Keep it Simple Stupid).

Thanks again,

Seals

SenCeliextito · 2012-02-03 23:20:04

Word of warning about the CAS exams. The CAS has, over the last 3 years been trying to change the approach to their upper level exams. The seem to have been having a hard time getting it right as evidenced by the large volatility in the pass rates over the last 3 years and a pass rate that was the lowest in 20 years for this last fall sitting.

You probably want to take these historical travel time averages with a grain of salt. When 3/4 to 1/2 as many students are passing as they did in the past on exams and on exams that are administered only once a year at that, you are likely going to get a lengthening of that average travel time.

The SOA seems to have better calibrated their upper levels exams and modules than the CAS has at least thus far.

Math Is Fun Forum

#1 2011-08-31 09:13:19

Time Weighted average Help

#2 2011-08-31 18:59:49

Re: Time Weighted average Help

#3 2011-08-31 19:19:59

Re: Time Weighted average Help

#4 2011-08-31 19:48:15

Re: Time Weighted average Help

#5 2011-08-31 19:56:15

Re: Time Weighted average Help

#6 2011-08-31 19:59:54

Re: Time Weighted average Help

#7 2011-08-31 20:02:46

Re: Time Weighted average Help

#8 2011-08-31 20:04:26

Re: Time Weighted average Help

#9 2011-08-31 20:05:28

Re: Time Weighted average Help

#10 2011-08-31 20:14:21

Re: Time Weighted average Help

#11 2011-08-31 20:19:08

Re: Time Weighted average Help

#12 2011-08-31 20:30:25

Re: Time Weighted average Help

#13 2011-08-31 20:36:42

Re: Time Weighted average Help

#14 2011-08-31 22:35:36

Re: Time Weighted average Help

#15 2011-08-31 23:15:23

Re: Time Weighted average Help

#16 2012-02-03 23:20:04

Re: Time Weighted average Help

Board footer