A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Scoring or Rating



 
 
Thread Tools Display Modes
  #1  
Old June 19th, 2010, 11:44 PM
JamesZ JamesZ is offline
Member
 
First recorded activity by OfficeFrustration: Jun 2010
Posts: 1
Default Scoring or Rating

From a previous post. Thought this example might be easier to understand.

I have an Evaluation Sheet that has four cells of different ratings each represented in a cell per rating: i.e.

Cell 1 represents an Excelent rating
Cell 2 represents a Good rating
Cell 3 represents a Fair rating
Cell 4 represents an Unsat rating

There are 7 total people rating on the Evaluation Sheet
I need to average the scores from the four cells to another cell. The total scores add to 7.

Example 1:

(Excelent) Cell 1 = 2
(Good) Cell 2 = 1
(Fair) Cell 3 = 2
(Unsat) Cell 4 = 2

or

Example 2:

(Excelent) Cell 1 = 5
(Good) Cell 2 = 0
(Fair) Cell 3 = 2
(Unsat) Cell 4 = 0

How do I get the average score in Cell 5?

Please Help!

Thanks,
JamesZ

Last edited by JamesZ : June 20th, 2010 at 04:53 AM.
Ads
  #2  
Old March 15th, 2011, 11:25 PM
tarquinious tarquinious is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 34
Default

Quote:
Originally Posted by JamesZ View Post
From a previous post. Thought this example might be easier to understand.

I have an Evaluation Sheet that has four cells of different ratings each represented in a cell per rating: i.e.

Cell 1 represents an Excelent rating
Cell 2 represents a Good rating
Cell 3 represents a Fair rating
Cell 4 represents an Unsat rating
Hi there JamesZ.

Well, I'm sure there are way smarter answers available, however for the sake of getting you an answer, I have come up with the following.

Firstly, might I suggest re-ordering from Unsat to Excellent. The reason being that it will make an HLOOKUP work for you later on...

Let's start by assigning a value to each mark. Let's call Unsat 1, Fair 2, Good 3 and Excellent 4. This will allow you to take an average and work out what their grade should be.

Now put those values in a row above each grade. You should end up with 1, 2, 3, 4 in cells A1 to D1 and Unsat, Fair, Good, Excellent below them in cells A2 to D2.

Now the tricky bit. In VBA, copy and paste the following code into a new module. If you need details on how to do this, let me know and I can post the instructions.

Code:
Function GetAverageRating(Excellent, Good, Fair, Unsat)
    Dim Score, x As Integer
    ScoreNum = 1
    If Excellent + Good + Fair + Unsat = 7 Then
        If Excellent  0 Then
            For x = 1 To Excellent
                Score = Score + 4
            Next x
        End If
        If Good  0 Then
            For x = 1 To Good
                Score = Score + 3
            Next x
        End If
        If Fair  0 Then
            For x = 1 To Fair
                Score = Score + 2
            Next x
        End If
        If Unsat  0 Then
            For x = 1 To Unsat
                Score = Score + 1
            Next x
        End If
        GetAverageRating = Score / 7
    Else
        GetAverageRating = "Not 7 Results"
    End If
End Function
Back in your spreadsheet, next to the row of results, type in the formula:
=GetAverageRating(D3,C3,B3,A3)

For your example where you had 2 Unsats and 5 Goods, this will result in an average of 2.428571.

Now you can do several smart things with this number. Firstly you can decide if you want to round it up, round it down, round it based on the decimals, or leave it as it is. To do this, you can use one of the following:
Code:
=ROUNDUP(GetAverageRating(D3,C3,B3,A3),0)
=ROUNDDOWN(GetAverageRating(D3,C3,B3,A3),0)
=ROUND(GetAverageRating(D3,C3,B3,A3),0)
Next, you can use the result to return the grade by using an HLOOKUP (the reason we reversed the order of the grades from 1 through to 4 is because HLOOKUP requires vaules in ascending order).
Code:
=HLOOKUP(ROUND(GetAverageRating(D3,C3,B3,A3),0),$A$1:$D$2,2)
Have a play around with the rounding and lookup depending on how kind you want to be with the grades.

Let me know if anything is unclear or you'd like more details.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:18 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.