View Single Post
  #2  
Old March 16th, 2011, 12:25 AM
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.