Forum Discussion
Formula to extract results with criterial
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.
6 Replies
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.
- 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. : )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?
