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 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!
Best Answer
-
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 <> "")
Answers
-
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.
-
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?
-
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?
-
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?
-
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.
-
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?
-
=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 <> "")
-
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?
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
