Excel Formula - Calculating Total Percentage

0 votes

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 by Kithuzzz
• 28,900 points
59 views

1 answer to this question.

0 votes

You just need the AVERAGE of all the percentages.

=AVERAGE(F2:F26)

This will work for column D.

answered Dec 28, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Excel Formula calculating the starting date of a given calendar week

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

answered Dec 10, 2022 in Others by narikkadan
• 53,520 points
79 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
300 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18, 2022 in Others by Edureka
• 13,630 points
106 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 53,520 points
128 views
0 votes
1 answer

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

answered Oct 8, 2022 in Others by narikkadan
• 53,520 points
163 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 53,520 points
129 views
0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 53,520 points
141 views
0 votes
1 answer

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

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

answered Nov 24, 2022 in Others by narikkadan
• 53,520 points
147 views
0 votes
1 answer

Frequency Array formula EXCEL not calculating

You obtain the same outcome, which suggests ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 53,520 points
59 views
0 votes
1 answer

PHPExcel generated excel file not auto-calculating formula

By recalculating on the server side, it ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 53,520 points
218 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP