Weighted Average

[Robin 0830]

I have a client who wants to give 80% of a rank and 20% of seniority to calculate the order in which his employees can bid for their shift. I'm not certain where to start to figure out how to weigh this since this is so unusual.


For example, the rankings will be approximately 1-14 and their seniority I calculated based on their hire date and todays date. I used NETWORKDAYS to see how many days they've been employeed. How do I figure out how to weigh these two stats as noted above in order to figure out which employee should be able to bid first, second, etc.?


Thanks!

 

[Sajan]

Hi Robin,

You could use the following formula to determine the percentile for a given person. (The higher the percentile, the eligbility to bid for their shift would be higher. For example, someone with 100% would bid first.)


I have assumed that Rank #14 is the highest (best) rank, and Rank #1 is the lowest rank.


Assuming that Seniority for the first employee is in A2, and Rank is in B2, put the following in C2

=(A2/MaxSeniority)*0.2 + (B2/MaxRank)*0.8


You can format the above result as a percentage, then sort by the values.


Cheers,

Sajan.

 

[Luke M]

Agree that your boss left that rather vague. Here's the interpretation I would use:


In order to compare rank and seniority properly, we'll want to give them the same scale (1-14). Thus, once you have their employed time, you can use the RANK function to put rank their seniority. Weighted average then is:

=Rank*80%+Seniority*20%


Note that this does have a small chance of creating duplicate values. In case of ties, I would defer to the 80% value to determine which one to go first. Depending on what this is for, you may just need to get more clarification from your boss on what they meant.

 

[NARAYANK991]

Hi Robin ,


I do not know whether what I am putting down is different from what Sajan and Luke have posted , but let me do it for clarifying my own thoughts.


Let me assume the rank varies from 1 to 14 , with 1 having the best rank , while 14 is the lowest rank.


When you assign a weightage to this , what you are saying is that a rank of 1 will correspond to 0.8 or 80 % , and a rank of 14 will correspond to 0.0 or 0 % ; thus each rank will correspond to a slab of about 0.06 ( 0.8 divided by 13 ) i.e. 1 corresponds to 0.8 , 2 corresponds to 0.74 , 3 corresponds to 0.68,..., till 14 corresponds to 0.


Now , if we assume that the seniority varies from 1000 days to 5000 days , the maximum seniority of 5000 days will correspond to 0.2 , while the lowest seniority of 1000 days will correspond to 0.0 ; thus every 100 days will correspond to 0.005


Thus , the composite weighted average is obtained by just looking up the rank and seniority within a lookup table , unless you actually want to calculate the result.


For example , a person who has a rank of 2 , and a seniority of 3000 days , will have a result of 0.74 + 0.1 = 0.84 ; where this puts him / her in the overall list depends on the other results.


Narayan

 

[tvst]

Hi Robin,


It is very simple but be careful not to calculate the seniority in days....! convert it in years. b'coz then only data would be compatible (compatible to ranks ranging from 1 to 14) as even 20% of 1000 days of seniority would be 200 days and will lead to misinterpretation.


otherwise use the method depicted by SAJAN (A2/MaxSeniority)*0.2 + (B2/MaxRank)*0.8)


both the things i have checked and have compared the results. they yield the same results.