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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Quote:
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 =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) Code:
=HLOOKUP(ROUND(GetAverageRating(D3,C3,B3,A3),0),$A$1:$D$2,2) Let me know if anything is unclear or you'd like more details. |
Thread Tools | |
Display Modes | |
|
|