Aug 30 2021 07:18 PM
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.
Aug 30 2021 10:06 PM
@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.
Aug 31 2021 07:07 AM
Aug 31 2021 08:16 AM
@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?
Aug 31 2021 04:12 PM
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.
Aug 31 2021 10:43 PM
@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.