Sum of Weigthings must be within 100%

Hi all,

I have a scenario whereby I need to assigned weightings to each of the Actions assigned. The total of the weightings MUST BE 100%. The catch here is there is no fixed number of Actons. A superior can assign 4 actions to student while another superior may assign 6 actions to another student. No matter how many actions assigned, the weightings shall be 100%.

Any thoughts how to go about it ... I was thinking to use Data Validation but it seems not workable, maybe I could have missed out some important steps. Any suggestions are appreciated.

Can this be done?

Alf, I think you got my intention wrong. Christopherp is closer to what I want.

Let me rephrase my question. What I need is to check and ensure that column C total is 100%. Regardless of how many items assessed in Column B, the total for column C must be 100%

For example :-

1. Let say Column B has 4 items, Column C should have 4 weightings totaled to 100%.... it could be 20%, 30%, 15%, 35% or 12%, 18%, 40%, 30%.

2. Let say Column B has 5 items, Column C should have 5 weightings totalled to 100% ... again it depends on what item was assessed and the weightings could be varying. However, the total must be 100%.

3. Let say Column B now has only 1 item. Column C should have only 1 weighting and since it is only 1, the weigthing has to be 100%. If they user only enter 20%, the validation will stop the user from entering the 20% because it is below 100%.

Can such be handled without complex programming?

Any help here ???

You could assign the priority as arbitrary numbers, and then normalize the weights by dividing by their sum.

Originally Posted by shg

You could assign the priority as arbitrary numbers, and then normalize the weights by dividing by their sum.

Can you elaborate further... I think you have misunderstood my intention.

Please Login or Register  to view this content.

It's hard for me to explain ... but I think I figure an alternative method.

Thanks for all who contributed the idea.