CODEWITHSUNDEEP
excelexcel-formuladistribution
Shan
Shan

Reputation: 13

Formula to make a score based on how evenly 5 groups are distributed

I have 5 buckets of age ranges: Age Less than 25 years, Age 25-34 years, Age 35-44 years, Age 45-54 years, Age 55 years and more

I want a formula to create a score based on how evenly 5 groups are distributed. For example if there are the same number of people in each of the 5 buckets I want the score to be 100 and if only 1 of the 5 buckets has people in it (worst distribution) I want the score to be zero.

Upvotes: 1

Views: 324

Answers (1)

Ralph
Ralph

Reputation: 9444

If you are placing the values for the five buckets into the cells A1 through E1 then the following array formula should give you a pretty good estimate:

=STDEV.S((A1:E1)/AVERAGE(A1:E1))/2.25

Note that this is an array formula and needs to be entered as such using Ctrl+Shift+Enter (instead of just Enter). You will see that Excel recognizes the formula as an array formula by automatically adding curved brackets around your formula (no need to add them yourself):

{=STDEV.S((A1:E1)/AVERAGE(A1:E1))/2.25}

Explanation: Basically, standard deviation is by definition what you are looking for: "a measure to quantify the amount of variation".

Yet, the resulting value of a standard deviation would be dependable on the underlying values and would not give you an even-spread from 0% to 100%. Hence, I implemented a comparative measure which is the same for all values using the AVERAGE(). Yet, you could also use a SUM() instead. Afterwards you set one of the “buckets” to any number and the rest of the buckets to zero and in turn you’ll get the divisor you need to ensure that the result will be always between 0 and 100.

So, the result for {=STDEV.S((A1:E1)/AVERAGE(A1:E1))} will be 2.23607... which I rounded to 2.25. With {=STDEV.S((A1:E1)/SUM(A1:E1))} you'll get something like 0.44721... which could be rounded to 0.45.

I am sure that a more savvy user here might be able to explain you how to calculate this last bit of information too instead of merely estimating it as I do. But unfortunately, I am unable to do so. But if you are really curious then you might want to re-post this question on Data Science and I am sure you'll find someone to explain this to you in mathematical terms.

Upvotes: 1

Related Questions