Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 70
Default What is, and should I use Weighted Average?

Hi,

I calculate Sickness and Absence (S&A) data for my company, by department.
Each department can have between 5 - 100 people in it. I currently do a S&A
% of the department and then as company as a whole.

Because in one team we could have 5/10 people off = 50% S&A rate, yet in
another department 5/100 = 5%.

Is there a better way of calculating the % S&A for each department that will
give more meaningful data?

Thanks in advance,

AW
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default What is, and should I use Weighted Average?

Haven't you answered the question yourself, calculate the average per
department based on the number in that department.

Other than that, you have given us nothing to go on.

--
__________________________________
HTH

Bob

"ArcticWolf" wrote in message
...
Hi,

I calculate Sickness and Absence (S&A) data for my company, by department.
Each department can have between 5 - 100 people in it. I currently do a
S&A
% of the department and then as company as a whole.

Because in one team we could have 5/10 people off = 50% S&A rate, yet in
another department 5/100 = 5%.

Is there a better way of calculating the % S&A for each department that
will
give more meaningful data?

Thanks in advance,

AW



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default What is, and should I use Weighted Average?

Good question!!

Never average the percentages.
Always get the totals and then calculate a percentage:

(5+5)/(10+100)=
10/110=
9.09%

--
Gary''s Student - gsnu200825


"ArcticWolf" wrote:

Hi,

I calculate Sickness and Absence (S&A) data for my company, by department.
Each department can have between 5 - 100 people in it. I currently do a S&A
% of the department and then as company as a whole.

Because in one team we could have 5/10 people off = 50% S&A rate, yet in
another department 5/100 = 5%.

Is there a better way of calculating the % S&A for each department that will
give more meaningful data?

Thanks in advance,

AW

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default What is, and should I use Weighted Average?

ArcticWolf wrote:
Hi,

I calculate Sickness and Absence (S&A) data for my company, by department.
Each department can have between 5 - 100 people in it. I currently do a S&A
% of the department and then as company as a whole.

Because in one team we could have 5/10 people off = 50% S&A rate, yet in
another department 5/100 = 5%.

Is there a better way of calculating the % S&A for each department that will
give more meaningful data?

Thanks in advance,

AW



You would use a weighted average for the company as a whole. With your example
above, the company would have a S&A rate of 9.09%, which is calculated as total
"off" / total people [(5 + 5) / (10 + 100)]. That would be different than if
you would "average the averages" [(50% + 5%) / 2 = 27.5%].
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default What is, and should I use Weighted Average?

ArcticWolf wrote:
Hi,

I calculate Sickness and Absence (S&A) data for my company, by department.
Each department can have between 5 - 100 people in it. I currently do a S&A
% of the department and then as company as a whole.

Because in one team we could have 5/10 people off = 50% S&A rate, yet in
another department 5/100 = 5%.

Is there a better way of calculating the % S&A for each department that will
give more meaningful data?

Thanks in advance,

AW


If you are trying to get to a company average others have steered you
well. But you are asking about meaningful data for departments, and I
think you are already there.

If it is normal to have an S&A rate of 9% company wide, a department
with a rate of 50% is clearly above normal, even within a reasonable
margin of error, and despite that department having a staff of 10.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Weighted Average Jd.willis Excel Worksheet Functions 4 October 30th 08 03:28 PM
weighted average elaine9412 Excel Worksheet Functions 6 August 28th 08 07:40 PM
Weighted Average [email protected] Excel Discussion (Misc queries) 1 July 23rd 07 07:04 AM
Need help with weighted average [email protected] Excel Discussion (Misc queries) 2 December 7th 06 07:15 PM
Weighted Average phm New Users to Excel 0 February 2nd 06 05:39 PM


All times are GMT +1. The time now is 04:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"