Hello Experts, attached will give you more clear picture of what am I trying to achieve.
In short (the example is oversimplified for clarity):
Mom, Dad, and little Sarah are deciding on a house purchase. They narrowed down their choices to 3 houses: House A,House B, and House C. They have created this chart (see attached) and assigned a score to each important criteria they deemed necessary. They now want to use this tool in order to determine which house is the best option.
I want to make this chart "dynamic" e.g. the WEIGHT column (column B) needs to always add up to 100% and each criteria item to be able to be assigned a different weight (currently all items have an equal weight of 20), depending on who is making the decision, for example:
If it is up to little Sarah to make the decision she would place more weight on "close to school " and 'kids to play' items, Mom wants to give a higher weight on 'close to mall' and dad, for some reason, assigns the highest weight on 'close to pub' criteria.
How can I build this table\tool so I can utilize it for simple decision making by changing the weight and individual scores as well?
Please include in the answer any formulas and accurate cell references I need to use.