Excel Formula - Calculating Total Percentage

Question

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?

Answer 1

You just need the AVERAGE of all the percentages.

=AVERAGE(F2:F26)

This will work for column D.