Stacked Bar Chart with Percentages

04/08/21
Answered - Pending Review

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!

Previous1

Answers

  • Paul NewcomePaul 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.

  • 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 NewcomePaul 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?

  • edited 04/08/21

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

  • My formula is =AVG(CHILDREN())

  • Paul NewcomePaul 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?

  • 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 NewcomePaul 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.

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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


  • Paul NewcomePaul 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 <> "")

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

  • Paul NewcomePaul 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?

Sign In or Register to comment.