Weightings

[beepbeep]

Hello,

I'd really appreciate some help in understanding the best approach to calculating / applying weightings to a set of criteria in order to prioritise a list of records.

Let me give an example: identifying "who is most at risk" in set of households.

For each household record, there is a set of criteria (let's also suppose that some work has been undertaken to associate a level of risk to each) e.g.

1. lifestyle of household - (7 bands VH, H, AA, A, BA, L, VL)
2. distance from help - (4 bands VH, H, L, None)
3. known to external agency (Y / N)
4. 80+yrs of age? (Y / N)
5. had previous intervention (Y/N)

Each household can have any permutation of the above criteria, so I end up with:

Address1,VH,L,N,Y,Y
Address2,L,VL,Y,N,Y
Address3,H,H,N,Y,N etc.

What I'd like to be able to do is understand how I can weight these criteria in a sound, logical way, to identify which combinations are the most risky

i.e which is riskier - a household with a "very high risk" lifestyle but distance from help is "low risk", or a household with "high risk" lifestyle but distance from help is "high risk"

Is there a mathematical way for identifying how much weighting should be applied? I can then append this info to the end of each record.

Hope that makes sense! Very much look forward to hearing back! Thanks in advance.

 

[chirayu]

Something like this.

 

[NARAYANK991]

Hi ,

My opinion is that what you want done cannot be done only by using Excel ; on its own Excel cannot decide which is riskier , a resident who is 80+ years old , or a resident who has a risky lifestyle but is not 80+ years old. Obviously both of the above will be less risky than a resident who is both 80+ years old and has a risky lifestyle !

Only records ( data ) can provide this information.

Narayan

 

[beepbeep]

Thanks chiaryu for such a prompt reply!

The sample spreadsheet you created for me, weights the responses within each criteria set (i.e. VH is of greater 'importance' than H etc), but I'm trying to do something a little different to that...

I am trying to identify the weight that should be given between each criteria set

e.g. out of the five example sets I gave (lifestyle, distance from help, etc.), which ones contribute most to the overall 'riskiness' of that household?

For example, say the household has a lifestyle risk of "high", what weight should that "high" impart against the other 4 criteria.

 

[beepbeep]

Hi Narayan,
Thanks for the reply!

Yes - I know Excel isn't a crystal ball (almost though, right?)

I suppose, if there are several factors which contribute so how 'at-risk' a household is, I'm trying to understand what kind of data I need (or the type of analysis that needs to be undertaken) in order to provide this information.

e.g. am I simply looking for the criteria that has the closest correlation with, say, 'numbers of incidences', or is there some specific statistical analysis that can be undertaken to determine which data is the most significant?

 

[NARAYANK991]

Hi ,

As I have mentioned , only statistical data will be able to guide us.

For example , if the mortality rate among 80+ year olds is greater than the mortality rate among those less than 80 year olds but who have a risky lifestyle , then we can weight the latter category appropriately compared to the former.

For example , take the case of previous intervention ; who can say whether those who have had a heart attack stand a greater chance of dying than those who haven't ? After all , if I have had a heart attack and survived , I might be prompted to change my lifestyle for the better , compared to someone who has not had a heart attack , and for whom the very first attack may prove fatal.

Narayan

 

[chirayu]

If you have historical data for these criteria then you just need to figure out the various permutations you could get & then look at the % of occurrence for each permutation to figure out the risk. Also another factor you need to take into account is the area in which the address is. Some areas might have a higher risk for a similar permutation than another. Attached is a list of permutations I found.

 

[Hui]

To decide weightings where mutiple criteria are involved you need to decide the logic behind the weightings and especially how a high score in the 2nd or 3rd class compares to a low value in the 1st class or ratings

It is typical to give each Category of weightings a different weight eg:

Category 1 might be out of 100
Category 2 might be out of 30
Category 3 might be out of 10
etc

Or you may want it so that Multiple lower Categories can overlap
Category 1 might be out of 100
Category 2 might be out of 70
Category 3 might be out of 50
etc

This way high scores in Category 2&3 can outweigh a high score in Category 1

As the others said the use of historical data can be helpful in determining and testing ideas

 

[beepbeep]

sorry for the slow reply to the thread
thanks guys - I think I need to go back and look at how best to capture and then analyse any historical data that I may have, in order to find that 'correct/logical' weighting to each category.