I'm attempting to determine the overall completion percentage of 24 distinct tasks, each of which may have a different maximum value.
The maximum value for each activity in columns C and D is always 175; thus, I take that value and divide it by cell H1. I then use the following calculation to determine the overall percentage of column C that has been completed:
=(SUM(C2:C13)+SUM(C15:C26))/((COUNTA(C2:C13)*$I$1)+(COUNTA(C15:C26)*$I$1))
This also applies to those columns. The overall proportion of columns E and F that have been finished is where I am encountering issues. The maximum amount is inconsistent, unlike in the previous example. I can figure out the percentage of each task completed, but I can't get the total percentage to work or figure out the right total.
I have tried using a SWITCH but this does not work:
=(SUM(E2:E13)+SUM(E15:E26))/((COUNTA(E2:E13)*SWITCH(G2,"W9",$I$2,"W10",$I$3,"W11",$I$4))+(COUNTA(E15:E26)*SWITCH(G2,"W9",$I$2,"W10",$I$3,"W12",$I$5)))
I have put a copy of the spreadsheet in a Google Drive if that would help: https://drive.google.com/file/d/1p8JOMWqYDPaRwYy1AQ3X1jxTHAmigYIW/view?usp=sharing
Would anyone be able to share how to get this formula working?