Formula to extract results with criterial

[choongko]

Being new to excel, I am having some difficulties trying to write a formula for part of my dashboard to retrieve scores for a test results.  Basically, to explain the attached excel sample data:

1.  There are 3 main tests. Each Test has its evaluated Tasks and subtasks (Labelled as TaskID and SubTaskID), and they are taken by different groups of participants (labelled as GrpID). 

2.  The tests are tagged to 4 conditions: Mandatory, Critical, Performance, Safety. and the Scores attained range from 0 to 5, and NA.  

3.  To prepare for the main calculation of scores, I need to prep the data as shown in the "checklist dashboard excel 2016" worksheet.  There are two tables:  In the first table (Records of evaluated tasks), I need to retrieve the scores of the evaluated tasks and subtasks for each Grp, separated in their conditions column.     

For the second table, it was suppose to list the unique tasks evaluated, and derive the minimum score for mandatory conditions, and average the scores for the other three conditions accordingly in their respective columns and rows. 

4.   After this, I would be using these data to proceed with the next stage of calculation.  I have reached that stage yet as I was stuck for a couple of days in this issue. 

I hope to have solutions for excel 2016 and 360.  Appreciate the advice from this community.  Thanks.

[#2]

@choongko Not sure if I have understood everything, but it seems you can achieve the type of summary described with one or more pivot tables. Have included one in the Checklist sheet as an example. See attached. Note that I transformed the Raw Data into a structured table. Furthermore, the "numbers" in the Score column were in fact texts, so I changed them to real numbers with the help of Text-to-columns. Now the pivot table can calculate the minimum and average scores.

[#3]

Thanks for your response. Looking at your solution, I think you might not have understood my intent. My bad, the computation module may be a bit confusing.

Let me clarify with an example: Task 1101 has 10 subtasks, 3 mandatory subtasks (Scores 3, 4, 2) , 2 critical subtasks (3,2) , 3 performance subtasks (3, 4, 5), and 2 safety subtasks (2, 4). In this situation, the min Mandatory score would be 2; critical score would be 2.5 (avg); performance would be 4 (avg); safety would be 3 (avg).

These results are required for the next computation, which I think I have the solution. It's the latter that I was stuck. I did tried using pivot table and I thought it is restrictive, and I could not summarized the value. Hope I have clear your understanding. : )

[#4]

@choongko Sorry, you have lost me. Perhaps we are not looking at the same data. Task 1101 has 8 sub-tasks (1 mandatory, 7 performance). Why not fill in your schedule with manually calculated values, explaining the logic for whatever calculation you need?

[#5]

@Riny_van_Eekelen 

Thanks for being patient.  I have done up entire process (5 steps/tables) of this calculation for better understanding.  Pse refer to the worksheet 'checklist dashboard excel 2016'.   Because the process is a bit complicated, I chose not to use pivot table.  Appreciate your advice.   

[#6]

@choongko Thanks for explaining. Still don't really follow all the steps and the logic, but that may be my lack of imagination. One thing I can resolve for you, though, is the question of why some of the formulae return #N/A. That's because the underlying cell (K12) contains 2.85. This value does not exist in the "MPCS score" column where all scores are numbers with only 1 decimal. You need to round K12 to 2.8 or 2.9, whatever the logic is that you applied to determine the "Percent X" column in the first place.

[#7]

Thanks.