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?

Dec 28, 2022 in Others 371 views

## 1 answer to this question.

You just need the AVERAGE of all the percentages.

`=AVERAGE(F2:F26)`

This will work for column D.

• 63,720 points

## Excel Formula calculating the starting date of a given calendar week

The number of days to take into ...READ MORE

## MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE

## How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE