+ Reply to Thread
Results 1 to 8 of 8

Reallocating Values Based on Percentages and Weights

  1. #1
    Registered User
    Join Date
    11-07-2014
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    6

    Reallocating Values Based on Percentages and Weights

    I am having difficulty in coming up with a formula that will assign point values based on a given number of points and the weights assigned to them.

    I have two indicators that are assigned a score of 1-100. The scores will be weighted based on criteria and each indicator will be assigned a point value. The criteria/rules for the calculation are:

    1. Assume A is weighted at 60% and B at 40%.
    2. The maximum point value assigned to A is 16 and the maximum point value for B is 24.
    3. Total point value for A+B=40.

    i.e. if A and B both receive 100 points then A is assigned 16 points and B is assigned 24 points.

    I have attached an example of the layout, but I am struggling with the formulas to make the calculations work.

    Thanks you in advance for the help!
    Attached Files Attached Files

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Reallocating Values Based on Percentages and Weights

    Isn't your weighted score simply the [actual score]*[weight]*[max for a or b]?

    For example if score for B is 50% then weighted is 0.5 * 0.4 * 24 = 4.8
    Happy with my advice? Click on the * reputation button below

  3. #3
    Registered User
    Join Date
    11-07-2014
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    6

    Re: Reallocating Values Based on Percentages and Weights

    Crooza,

    Thank you for your reply.

    Using your formula then, if B is 100% then 1*.4*24=9.6. However, if the actual score is 100% then it should get the max point value of 24. It is transforming the actual score to the point structure that is giving me difficulty.

  4. #4
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Reallocating Values Based on Percentages and Weights

    I see what y've done. You've already applied the weighting to the score of 40 to get the 16 and 24. Just use [actual score] * [weight] * 40 then

    100% B would equal 1 * 0.6 *40 = 24
    50% b would equal 0.5 * 0.6 *40 = 12
    50% a would equal 0.5 * 0.4 *40= 8

  5. #5
    Registered User
    Join Date
    11-07-2014
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    6

    Re: Reallocating Values Based on Percentages and Weights

    That works, except I gave out the wrong instructions. The total points between A and B is 40, but A and B are not capped at 16 and 24. i.e. if A was 100% and B was 0 then all 40 points would go to A (and the total score would be 40).

    16 and 24 are actually another layer of weighting the scores. Therefore, A will have a weight of .4 (16/40) and weight of .6 and B will have .6 (24/40) and .4.

    I attached a new file for reference.

    Sorry for the mix up and thanks again for the help.
    Attached Files Attached Files

  6. #6
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Reallocating Values Based on Percentages and Weights

    Ok I'm not sure I follow so I'm guessing a little at what you're doing. Do you have any known results to test against other than A 100 and B 100?

    Let's try this

    If A is 100 and B is 100 then with equal weightings A would be 50% of 40 = 20 and B would be 50% of 40 = 20 but you have redistributed that weighting to 60/40 so 0.6/0.5 * 20 = 24 and 0.4/0.5 * 16

    So the formula you need for this is Aw = A*0.6/(A*0.6+B*0.4)*40

    And for Bw = B*0.4/(A*0.6+B*0.4)*40

    This will give you 24/16 for 100/100, 40/0 for 100/0, 0/40 for 0/100, and for 30/10 for 100/50 and 17.14/22.86 for 50/100

    I have no idea where you're going with the indicator weights though

  7. #7
    Registered User
    Join Date
    11-07-2014
    Location
    Washington, DC
    MS-Off Ver
    2013
    Posts
    6

    Re: Reallocating Values Based on Percentages and Weights

    Sorry for the delay. Just wanted to say thanks. Your last formula did the trick and I was able to modify it for the different scenarios I had.

    Thanks again, really appreciate the help.

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    1,899

    Re: Reallocating Values Based on Percentages and Weights

    Great

    Glad it worked. Mark the thread as solved if you haven't already.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to sum weights based on different elevations
    By bmoon171 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2014, 11:53 PM
  2. Replies: 7
    Last Post: 04-06-2014, 11:34 AM
  3. percentages based on two variables
    By rbsimpson in forum Excel General
    Replies: 10
    Last Post: 06-29-2012, 12:11 PM
  4. Values vs. percentages
    By OnTheCouch in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-08-2007, 05:27 AM
  5. [SOLVED] show difference between weights with correct pos and neg values
    By Sharon Durow in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2006, 01:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1