Excel Formula - Calculating Total Percentage

0
votes

I am trying to calculate the total percentage completed of 24 different activities where each activity could have a different max value.

For columns C & D - the max value of each activity is always 175 - so I take the value in C and divide it by cell H1. And then to calculate the total percentage completed by column C, I use this formula:

=(SUM(C2:C13)+SUM(C15:C26))/((COUNTA(C2:C13)*$I$1)+(COUNTA(C15:C26)*$I$1))

And this works for those columns. Where I am having trouble is the total percentage completed for columns E & F. The max value is not consistent like the prior example - I am able to calculate the percentage complete for each individual activity, but I cannot get the total percentage to either work or calculate the correct 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?

For clarification - column E is the current value for the character - column F is the % complete for the character which is based on the max value for each activity listed in cells H1-H7 and I1 - I7.

For instance, character A currently have 130 points in enchanting ( C1 ) and that activity has a max value of 175 ( I1 ) - which is 74.29% complete for that particular set of activities. But for Shadowlands, the current value is 79 points but this has a max value of 115 - so that activity is 68.7% complete.

So what I am showing in column G is the maximum value for each of the activities for Shadowlands. And my end goal is to have the completeness in % show in F27 - which calculates the total number of activities currently have divided by the total number possible. And the legend on the right side of the spreadsheet shows the maximum number of points for each activity.

I updated the image to show what I would be expecting if my formula was correct. E27 is the summation of current activity points G27 is the total number of available activity points F27 is the percentage complete

Ideally I would not need column G - the formula in F27 could look up the correct amount of maximum points.

enter image description here

1
excelexcel-formula
Where to get the max value for Tailoring/Engineering/Mining (values not in column I) ? – p._phidot_
I'd advise you first to write the formula in a mathematical way, and then try to convert it to Excel cell references. In top of this, you might use names, instead of absolute references like $I$1. – Dominique
I updated the question to add more clarification. – MISNole

1 Answers

0
votes

The answer will look too simple but you just need the AVERAGE of all the percentages.

=AVERAGE(F2:F26)

It will also work for column D. It represents the same mathematical logic as in your working formula.