Increase, No Change, and Decrease Graph

Question

I am attempting to make a template of a graph my company will use for reporting on data regarding risk scores in health assessments.  The graph would need to depict # of individuals who increased in risk score, no change, or decreased in risk score for 6 different health categories.  The format I've used is a bar graph however my issue is for the no change group this adds into the positive change group.  Ideally I would like for the no change group to just have a call out box with the number of individuals who showed no change while only the positive and negative scores reflecting above(increase in score) or below (decrease in score) the X-axis.  Is this possible?  The work around I've done is to just simply color the no change and positive bars in the same color and make the call out box pointing to the axis, however the bar for the positive groups looks higher than it should as it is combining the positive and no change bars.

Secondly, I would like the total number of participants in each category to be show below the Risk category labels.  This chart has been created with text boxes over-lay onto the graphs, but for streamlining the process and efficiency if there is a way that it would update with updated pivot tables that would be amazing.

I will attach a picture to hopefully make sense of this.

I hope this makes sense, thank you.

Answer 1

Thank you for your help, I may have figured out a solution that works for making a template and easily updating the graph by data set.

The only thing I can't figure out is the numbers under each risk category showing the total for each group.

Here is what I have now:

Answer 2

original chart is just excel, with an image (the people on the 0 hash mark), and then text boxes on the graph where the custom text is..

I've actually figured out a way to do the graph that seems to work, a stacked 2d bar graph  for the positive and negative with the no change group being a line graph it seems to work, because for some reason the line stay's on the axis and allows me to put callout boxes for the numbers.

Answer 3

Where did you create that original chart? What program?

If you had 2016 you could try this new chart style

Create a box and whisker charthttps://support.office.com/en-us/article/Create-a-box-and-whisker-chart-62f4219f-db4b-4754-aca8-4743f6190f0d

In this next chart type you would have to add the no change as WordArt

Floating column charts

http://blogs.office.com/b/microsoft-excel/archive/2011/09/27/go-beyond-the-basic-chart-type.aspx

A floating column chart is great for comparing the low and high levels in a value range, represented by the bottom and top of the floating columns, as you can see in this daily blood sugar level ranges chart.

In this next chart type you could use the blue bar as the no change data:

Bar chart with lower & upper bounds [tutorial]

http://chandoo.org/wp/2014/01/08/bar-chart-with-lower-upper-bounds-tutorial/

Posted on January 8th, 2014 in Charts and Graphs , Learn Excel - 13 comments

Bar & Column charts are very useful for comparison. Here is a little trick that can enhance them even more.

Lets say you are looking at sales of various products in a column chart. And you want to know how sales of a given productcompare with a lower bound (last year sales) and an upper bound (competition benchmark). By adding these boundary markers, your chart instantly becomes even more meaningful.

This one looks like a good fit:

Excel Box and Whisker Diagrams (Box Plots).

http://peltiertech.com/WordPress/excel-box-and-whisker-diagrams-box-plots/

Peltier Tech Box and Whisker Chart Utility for Excel

http://peltiertech.com/Utility/BoxPlotUtility.html

Use Advances vs. Declines chart to understand change in values

http://chandoo.org/wp/2013/02/21/advances-vs-declines-chart/

Lets say you are responsible for sales of 100s of products (which belong to handful of categories). You are looking at sales of each product in last month & this month. And you want to understand whether sales are improving or declining by category. How would you do it?

You may be able to use the stacked chart that is part of the process of building this waterfall chart:

Waterfall Chart

http://blog.fusioncharts.com/2009/07/second-base-with-charts-understanding-the-waterfall-chart/

Here is a different approach to presenting those 3 data points:

Thermo-meter chart with Marker for Last Year Value

http://chandoo.org/wp/2012/06/11/thermo-meter-chart-with-last-year-marker/

*Thermo-meter charts*are very good to show how actual value compares with target (or budget). But how can we add another point for say Last Year value to the chart with out cluttering it*.*

Answer 4

I guess the answer is yes, but I suppose that rather depends on the data for the various divisions. It might, for example, be possible to set up a data range that covers most of the divisions (say {-100,100},{-80,120}) and then another for ALL, in which case you may only need two basic formats.

Regards

Murray