Stacked Bar Chart with Percentages

Sophie Knudson

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 country-level 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!

Paul Newcome

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 <> "")

Paul Newcome

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.

Sophie Knudson

Thanks so much Paul!

It sounds like maybe I need to re-structure 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 roll-ups?

Many thanks!

Paul Newcome

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?

Sophie Knudson

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

Paul Newcome

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

Sophie Knudson

My formula is =AVG(CHILDREN())

Paul Newcome

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?

Sophie Knudson

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

Paul Newcome

While there is a way to get things to work with your current setup, it gets rather complicated with multiple helper columns and semi-complex 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.

Sophie Knudson

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?

Paul Newcome

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

Sophie Knudson

=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 <> "")

Paul Newcome

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 <> "")

Sophie Knudson

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

Paul Newcome

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?