Average

[Rob]

could this be worked out in excel?



Thanks Rob



what is the average no of years service ?



No of years service No of people

Less than 1yrs 5
More than 1 less than 3yrs 10
More than 3 less than 5yrs 14
More than 5 less than 7yrs 9
More than 7 less than 9yrs 4
More than 9 +yrs 16

 

[Roger Govier]

Hi Rob

Obviously there is some source data that allowed the original allocation of
numbers of people into bands.
You need to work with this source data, total the number of years service
for all people and divide by 58 (the number of people in your sample table)

Regards

Roger Govier

 

[Guest]

Rob,

Roger's answer is the correct one. However, if you cannot get the data to
which he refers, you could try the following:

For each band, pick a representative number. For example, assume that the
people in the 1 to 3 year band worked for 2 years. The the 9+ you'll have to
make a wise choice. That "guess" could sway you final results.

Then do a sumproduct(avg, cnt)/sum(cnt)

Art

 

[Rob]

Thanks Roger and Art

but how do I enter:

"Then do a sumproduct(avg, cnt)/sum(cnt)"

This is not my problem but somebody else's
who is not connected to the net, and I am trying
to help her out.

If it would help you could send me a sample sheet.

Thanks again
Rob,

(e-mail address removed)

 

[Guest]

Try this:

A B C
1 Band People Avg (guess)
2 Less than 1 5 0.5
3 More than 1 less than 3 10 2
4 More than 3 less than 5 14 4
5 More than 5 less than 7 9 6
6 More than 7 less than 9 4 8
7 More than 9 16 12
8 6.146551724

Formula to get the answer in row 8 is:
SUMPRODUCT(B2:B7,C2:C7)/SUM(B2:B7)

If this doesn't line up right in your browser:
the last column reads: .5,2,4,6,8,12
the one before that reads: 5,10,14,9,4,16

Art