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