Solved

Weighted Rankings - MS Excel

Posted on 2014-12-11
4
166 Views
Last Modified: 2014-12-15
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.

Thank you,
RICUser
F--DOCS-DATA-ZZZ-Weight-and-Rankings.xls
0
Comment
Question by:RICuser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
Richard Obenchain earned 500 total points
ID: 40495098
Regarding your question about "always add up to 100%": the only way to do that is to have one of the values be calculated as 100-(sum of the rest).  That's probably not what you want.  However, a possibly-better solution would be to allow any value to be assigned and calculate the percentage.  So, for example, if someone enters (50,20,30,40,35) as the values, the page would itself calculate the percentages out of the total (175) and come up with (28.6, 11.4, 17.1, 22.9, 20).

For the second part "being able to be assigned a different weight... depending on who is making the decision", you'd have to have those individual decisions listed somewhere (this sheet or another) and then reference them by a dropdown or some such.

I'm not sure you can control the "all numbers have to be different" or why you would want to necessarily; if that were the case, you should create a ranking rather than a scoring system (e.g., 1 to 5 with the option of only selecting each number once).

Anyway, I attached a basic idea on tab two of one way you could do this.  It's kind of kludgy, but you could move the ranking tables off to another sheet if desired.
F--DOCS-DATA-ZZZ-Weight-and-Rankings.xls
0
 

Author Comment

by:RICuser
ID: 40499010
Thank you for your answer Richard.
As you can tell Excel is not my strong suit but I sense  that , thanks to your input, I am really close to achieve what I am trying  with this.
Of cours , the example I've given is oversimplified but in essence it is pretty much representative of what my goal is. To be more specific:
In my company we have several solutions (think houses A, B, and C) that we can offer to our clients for accomplishing the same task. HOW we deliver the solution differ and has different cost for the client (hence the different criteria we offer them ). Eventually, the client must select how important each of these criteria is for them (assigning the weight to each) and at the end  the result should show them WHY was a specific method selected before another . I am thinking of this like some kind of a decision making tool\decision tree. Often we get questions\complaints from clients questioning us as to WHY was this solution selected and not the other? Our goal is to show them that we took this route based on THEIR selections and the weight THEY themselves assigned to the different criteria.
So, instead of Sarah, mom, and dad , will that solution work with only one column, let's call it CLIENT and House A, B, and C will be Solution A, B, and C etc..? The individual scores for each criteria for each solution (-1, 0, and 1 color coded) is assigned by my team. My confusion from the spreadsheet that you provided comes from teh fact that column "dad" appears twice (the second appearance is as percentages) whereas Sarah and mom columns appear only once. Why is that so and will that work the same way if I remove Sarah and  mom columns and leave only one column call "CLIENT" ?
Would you modify your spreadsheet accordingly and re-attach please?
I appreciate your help with this!

Happy Holidays!
RICUser
(see sheet 3 from attached)
Weighted-Rankings.xlsx
0
 

Author Closing Comment

by:RICuser
ID: 40499012
Thank you Richard!
0
 
LVL 3

Expert Comment

by:Richard Obenchain
ID: 40500604
"Dad" appears a second time because it's a drop-down selection (you can change it to Mom, Sarah, or Average).

I've added a third sheet with the changes you requested.  I'm not sure this is the most appropriate method for what you're looking to do, but it should work.
F--DOCS-DATA-ZZZ-Weight-and-Rankings.xls
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question