Stacked Bar Chart with Percentages
Hi There!
I am trying to track and present progress on key milestones made by multiple countries to share with our donor.
I want to be able to show the average percentage completed overall for the milestone, but comprised of that show the individual countrylevel progress.
When I try to stack the bar chart of % complete, it adds the country values together rather than averaging them. Is there a way to do this with averaging rather than adding?
Here's what I am trying to create.
Thanks for your help!
Best Answer

Lets give this a whirl...
=SUMIFS([Burkina Faso % Complete]@row:[PAHO % Complete]@row, [Burkina Faso % Complete]@row:[PAHO % Complete]@row, @cell <> "") / COUNTIFS([Burkina Faso % Complete]@row:[PAHO % Complete]@row, @cell <> "")
Answers

You would need to establish the total when adding up all of the countries together for that particular bar.
So you have 2 countries which equates to a total percentage of 200% (or 2).
In the sheet where your grid is that the chart references, add another cell for reference such as "Remaining % Complete" and use
=2  ([Burkina Faso % Complete]@row + [Ghana % Complete]@row)
Then reference all three in your chart and change the color of the incomplete to white and don't show the label for that particular one.
If you are able to provide a screenshot of how your table is setup, we may be able to provide a more customized solution to your particular use case.

Thanks so much Paul!
It sounds like maybe I need to restructure my table. I've included a screenshot of what it currently looks like.
Would you recommend adding a column for each country rather than the rollups?
Many thanks!

The way you have it could work. Does you sheet currently have dependencies enabled, or are you using a formula for the parent level % complete?

I'm using an average formula of all the children rows. I couldn't figure out how to enable dependencies without date columns.

Is your formula referencing CHILDREN, or are you using specific cell references?

My formula is =AVG(CHILDREN())

And finally... Do you have other data sets like this under this particular set, or can we add another row at the parent level (but not an actual parent of anything) below it?

Not sure I'm fully understanding the question, but we have more rollups like the Submission to IRB. Some of them include more than two countries.

While there is a way to get things to work with your current setup, it gets rather complicated with multiple helper columns and semicomplex formulas.
Using a column for each country would definitely prove to be much easier. You could then add a column with this formula in it:
=(COUNTIFS([1st Country Column]@row:[Last Country Column]@row, [1st Country Column]@row:[Last Country Column]@row, @cell <> "")  1)  SUMIFS([1st Country Column]@row:[Last Country Column]@row, [1st Country Column]@row:[Last Country Column]@row, @cell <> "")
This will give you the remaining overall % complete left. Then you can include this in your chart, don't show the column label, and change that portion of the bar color to white.

Hi Paul,
I've added columns for each country, noting that some of them will be blank. (Example: Ghana doesn't need to translate the protocol, but Burkina Faso does.)
I added another column for the AVG % Complete and included your COUNTIFS formula but got #UNPARSEABLE error
Did I misunderstand the columns?

Can you copy/paste the formula directly from the sheet and provide a screenshot of your column headers?

=COUNTIFS([Burkina Faso % Complete]@row:[PAHO % Complete]@row, [Burkina Faso % Complete]@row:[PAHO % Complete]@row, @cell <> "") 1) SUMIFS([Burkina Faso % Complete]@row:[PAHO % Complete]@row, [Burkina Faso % Complete]@row:[PAHO % Complete]@row, @cell <> "")

Look like there is just a missing opening parenthesis before the COUNTIFS.
=(COUNTIFS([Burkina Faso % Complete]@row:[PAHO % Complete]@row, [Burkina Faso % Complete]@row:[PAHO % Complete]@row, @cell <> "") 1) SUMIFS([Burkina Faso % Complete]@row:[PAHO % Complete]@row, [Burkina Faso % Complete]@row:[PAHO % Complete]@row, @cell <> "")

I copied and pasted the formula and got a #INVALID OPERATION error

Hmm... It looks like the percentages are being linked in. Are you using a formula to pull the percentages in, or are you using a cell link?
How are they populated on the source sheet? Are you entering a number into a column formatted for percentages, or are you manually keying the "%" symbol when you enter the data?
Help Article Resources
Categories
Check out the Formula Handbook template!