Results 1 to 6 of 6

Math Help - Calculating a score in Excel

  1. #1
    Newbie
    Joined
    Oct 2011
    Posts
    4

    Calculating a score in Excel

    I have an Excel spreadsheet that I'm using to calculate a score based on how the evaluator rates a certain area. The area can have a score between 1 through 5 with 5 being the best. There are at times multiple evaluators. So for example there are eight evaluators, two of them rated the area "1" and the other six rated the area "2". The method I'm using to sum up the scores and output an overall average of the area, I'm getting higher numbers that I should be. The above example with my method is giving me a score of 2.8

    Just by using my head, I believe the score should be in the 1.7-1.8 range. The score should not be higher than 2 since there were no 3s scored. This is the formula I'm using for Excel:

    Code:
     
    =IF(B1>0,SUM(B1)*0.2,0)+IF(B2>0,SUM(B2)*0.4,0)+IF(B3>0,SUM(B3)*0.6,0)....

    BX contains the number of instances for that particular score, B1 refers to a score of 1. Help would be appreciated, thank you!
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Newbie
    Joined
    Oct 2011
    Posts
    4

    Re: Calculating a score in Excel

    So I figured out the proper score, 1.75. If you use AVERAGE(1,1,2,2,2,2,2,2) you get 1.75, which I believe is the proper score. Now the hard part is figuring out how to get Excel to manipulate the raw data into such a formula.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    -1
    e^(i*pi)'s Avatar
    Joined
    Feb 2009
    From
    West Midlands, England
    Posts
    3,053
    Thanks
    1

    Re: Calculating a score in Excel

    Quote Originally Posted by spin360 View Post
    So I figured out the proper score, 1.75. If you use AVERAGE(1,1,2,2,2,2,2,2) you get 1.75, which I believe is the proper score. Now the hard part is figuring out how to get Excel to manipulate the raw data into such a formula.
    If I understand correctly you can put the scores for each property into cells and then use the average function on cells. In my screenshot look for row 3 - I used your example figures.

    Since a picture says a thousand words:

    I have the spreadsheet saved if you want it (but I do not have an antivirus so you may wish to test it if you're on Windows)
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Newbie
    Joined
    Oct 2011
    Posts
    4

    Re: Calculating a score in Excel

    Well my only qualm with that is I cannot change the layout of the spreadsheet. So the formula would somehow have to decipher when there is a "6" in the cell, that means 6 evaluators gave it a score of say 1 or 2, depending on the location of the value. Sorry I did not clarify this in my first post.
    Follow Math Help Forum on Facebook and Google+

  5. #5
    -1
    e^(i*pi)'s Avatar
    Joined
    Feb 2009
    From
    West Midlands, England
    Posts
    3,053
    Thanks
    1

    Re: Calculating a score in Excel

    Quote Originally Posted by spin360 View Post
    Well my only qualm with that is I cannot change the layout of the spreadsheet. So the formula would somehow have to decipher when there is a "6" in the cell, that means 6 evaluators gave it a score of say 1 or 2, depending on the location of the value. Sorry I did not clarify this in my first post.
    So to be clear, you want a list of scores instead of a list of evaluators? Then in the main grid how many evaluators gave it that score?

    To compare with my screenshot "evaluator " would become "score" and the grid would be "0-8" depending on how many evaluators gave that score?

    Edit: Where the sum of b3 to f3 = 8 of course

    edit2: yet the average will always be 1.6?
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Newbie
    Joined
    Oct 2011
    Posts
    4

    Re: Calculating a score in Excel

    Quote Originally Posted by e^(i*pi) View Post
    So to be clear, you want a list of scores instead of a list of evaluators? Then in the main grid how many evaluators gave it that score?

    To compare with my screenshot "evaluator " would become "score" and the grid would be "0-8" depending on how many evaluators gave that score?

    Edit: Where the sum of b3 to f3 = 8 of course

    edit2: yet the average will always be 1.6?
    I think so? In my case the score would only go to 5 instead of 8. So in the row below the "score", the value that goes there would be the sum of evaluators that gave the area that particular score. Then some formula to average those scores. So...

    A B C D E F
    Score 1 2 3 4 5
    QTY 2 6 0 0 0
    Average score for the area = 1.75

    I hope that was clear. I cannot upload a screen shot of it anywhere so that is the best I can do.

    edit: so apparently vBulletin doesn't like extra spaces...
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. score
    Posted in the Statistics Forum
    Replies: 1
    Last Post: November 9th 2011, 11:14 AM
  2. Z-score.
    Posted in the Statistics Forum
    Replies: 1
    Last Post: May 26th 2011, 02:29 PM
  3. z-score
    Posted in the Advanced Statistics Forum
    Replies: 2
    Last Post: October 11th 2010, 10:00 AM
  4. Z score, t score, CLT? I don't remember.
    Posted in the Advanced Statistics Forum
    Replies: 5
    Last Post: June 24th 2010, 07:01 PM
  5. Replies: 3
    Last Post: September 22nd 2008, 06:45 AM

Search Tags


/mathhelpforum @mathhelpforum