1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Weighted averages in groups with common range

  1. Jul 26, 2012 #1
    I am doing a survey of questions grouped into categories. Each question has a weight applied to it. I want to then total and average each category. Lastly, I want to total and average all the categories together. Here's the challenge: I want all of categories and the total average to have the same range somehow.

    See attachment (png file) for a pic of the data I mocked up to describe my situation.

    The answers are a range of 1-5 (likert scale). So as you can see in the picture, the group ranges are 1-5, 1.45-7.25, and 1.90-9.50 respectively. However, the group is a completely different range 1.45-7.25. This is naturally happening because of the different weights on the questions. My end goal here is to give an average score for each category and the total score at the end similar to a FICO score. So the ranges must be the same for them to have the same effect. How can I do this with these weights in the mix?
     

    Attached Files:

  2. jcsd
  3. Jul 26, 2012 #2
    I just had a thought. Would it work if I made all of the weights in the category equal 1 (100%)?
     
  4. Jul 26, 2012 #3
    Scratch that, not it.
     
  5. Jul 28, 2012 #4

    haruspex

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member
    2016 Award

    Suppose the actual scores are Xa (total in category A), Xb, Xc. You still want the total score to be Xa+Xb+Xc (or dividing by total number of questions if you want the average), right? But you also want to quote average scores by category in a way that's consistent with the overall average.
    If the overall range is Y to Z, and the range for category A is Ya to Za, apply an affine mapping to the score Sa on category A: Sa->(Sa-Ya)*(Z-Y)/(Za-Ya) + Y. This maps Ya to Y, Za to Z, and everything in between in proportion.
     
  6. Jul 31, 2012 #5
    This seems to work pretty well. However, it does not seem to scale if I change the range. Is there a way to make the "grade" the same based on the range?

    Range = 1000-200
    CategoryA Score = 504.2
    Percentage Grade = 59.9%

    Range = 850-300
    CategoryA Score = 509.14
    Percentage Grade = 50.4%

    Excel formula
    =((AVERAGE(J6:J16)-D17)*($B$1-$B$2)/(E17-D17)+$B$2)
    504.20=(2.7273-1.0667)*(1000-200)/(5.4091-1.0667)+200
    509.14=(2.7273-1.0667)*(850-300)/(5.4091-1.0667)+300

    Entry Weight Score
    1 1.2 1.2
    2 1.3 2.6
    3 1 3
    4 1 4
    5 1 5
    1 1 1
    2 1.1 2.2
    3 1 3
    4 1.2 4.8
    1 1 1
    2 1.1 2.2
     
  7. Aug 1, 2012 #6
    I think I talked myself out of my concern. The reason I was concerned is if we ever change the range, it would mess with the quality of the scores. However, we are capturing the raw (non-weighted) data so we can re-calculate as needed. This is a very low possibility but fun to project either way. Your solution worked. This kind of reminds me of a bit field:
    http://en.wikipedia.org/wiki/Bit_field

    Thanks!
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Weighted averages in groups with common range
  1. Weighted Averages (Replies: 1)

Loading...