Solved

Dynamic Graphic - Stacked Horizontal Chart

Posted on 2011-09-02
17
332 Views
Last Modified: 2012-05-12
EE Professionals,

I have a simple table composed of two columns (Value 1 and Value 2) of numeric data (range 0.0 to 6.0).  I've attempted to build a HORIZONTAL STACKED BAR CHART that shows the level of Value 1 and the difference withl Value 2.  If Value 1 = "n/a" then it is considered not selected as a value to display.

Here is what I'm looking for;

The simple part:

1.) The horizontal axis needs to be reversed so that Text A (and associated values) is at the top.
2.) I need to have the horizontal axis reflect the words "Stage 1", "Stage 2", "Stage 3", "Stage 4", "Stage 5" as text appearing between the 1 - 5 ticks in the axis.

And this is the hard part;

3.) If the value in value 1 = "n/a" then the row needs to be removed from the graph with the graph reajusting to only show those rows with value (i.e. if value 1 <> "n/a" and value 1 or value 2 > 0, then display values.

Much thanks in advance,

B.


Adjusting-Graphic-v1.xlsm
0
Comment
Question by:Bright01
  • 10
  • 7
17 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello Bright,

re 1): either re-order your data table or format the Y axis with the parameters

- "Categories in reverse order" AND
- Horizontal axis crosses "At maximum category"

The first row of the data table will always be the closest to the X/Y axis zero point. If you keep that in mind, you can save yourself some grief when laying out horizontal chart data.

re 2): in a horizontal chart, the X axis is the value axis and by default will show numbers as the axis labels. To show text labels instead, I'd suggest to overlay the chart with an XY Scatter chart, with data points sitting at Y=0 and X=1 to 5,  and use the XY chart labeller to display the data point labels below the data points. Then format the original value axis of the horizontal bar chart to hide the labels, so that the data point labels of the XY chart appear to be the X axis labels.


re 3): you can use AutoFilter to filter out rows with N/A. Click a cell in the data table and then hit Alt-D-F-F (or use your preferred method to enable AutoFilter). After that, select the drop-down for Value1 and untick the row with "n/a" in the filter list.

Viola! the chart will not show that series.

(There are other, more complex ways to achieve the same result if you do not want to use the filter)

cheers, teylyn
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
See attached for all three points above in action.

cheers, teylyn
Adjusting-Graphic-v1.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

Greetings!  And much thanks for the response.  I was just implementing your recommendations when your sample came through.  I still have trouble with using an "overlay" graphic; your sample saved me quite a bit of time trying to do it.  

The challenge with Ref. 3 is that when I use the Filter, I do get the right result in the graph but here's how I'm using it.  I reset all the data to "n/a".  As data is collected, the cells ("n/a") become populated.  When they are filtered, they completely go away from the display.  Also, I have to filter it each time the data changes... I'm trying to make it dynamic.  Any ideas?

Again, thank you!

B.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
You can use some VBA to re-apply the current filter when a value in the sheet is changed manually

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.AutoFilter.ApplyFilter
End Sub

Open in new window


That code goes in the Sheet module. It works like this:

- you apply a filter, for example untick the "n/a", so the n/a rows don't show
- manually enter a "n/a" into one of the still visible rows - bang! the row will disappear
- click the filter icon in cell F2 and remove the filter and all rows show (Select all)
- restore valid numbers in all rows and then enter "x" into one row in column F
- Set a filter for column F to hide the "x" values
- edit another cell in column F and enter "x" -- the row will disappear.

This means that the filter is not limited to "n/a" as a value. Whatever filter criterion you set for the table will be re-applied after a cell is changed manually.

Mind you, this technique, using the Worksheet_Change event will only work if a cell is changed manually in the sheet. If the values are changed by a formula, you will need to use the Worksheet_Calculate event instead of Worksheet_Change.

cheers, teylyn

0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

I'm getting a compile error.  The F Column gets reset to "n/a" and I have added the macro to do it in module1.  I have added your code to the worksheet and given that the cells in column F are sourced from somewhere else, I changed the formula to the "Calculate event.

Help!

B.
Adjusting-Graphic-v2.xlsm
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Uhhmmm, ya.

That was the theory. When I use the code

    ActiveSheet.AutoFilter.ApplyFilter

in the Worksheet_Calculate event, my Excel 2010 crashes. Royally. Repeatedly.

Hmm.  -- I'm going to call in some more experts to have a look.

The Worksheet_Change macro works fine, though. Can you confirm that?

Will the "n/a" values be entered manually or will they be created by a formula in column F??

If they are created by a formula, you could click the "Reapply" button on the Data ribbon to  apply the same filter to the changed data.

In the attached file, I have used a formula to populate column F:

=IF(C4="n","n/a",B4)

The original values are in column B and in column C you enter a "n" if you want to create a "n/a" value in column F.

Enter a "n" in a visible cell in column C, then click "Reapply" on the Data ribbon and the data table filter (and the chart) will adjust.

Until someone more versed in VBA chimes in, that's the best I can deliver.

cheers, teylyn

Adjusting-Graphic-v1.xlsm
shot.png
0
 

Author Comment

by:Bright01
Comment Utility
Teylen,

Yes; The Worksheet_Calculate works fine.  My reset must include resetting both the cells as well as the filter and as you can see with v2.  OK... I just changed the reset to column C.....the source column and it does work with the Calculate function but I still have a problem with how to reset the filter and then auto engage it when column F begins to be populated from column c.

B.
0
 

Author Comment

by:Bright01
Comment Utility
I'm resubmitting v2 on this.  When a cell in C is changed from "n/a" to a number, it populates the graph appropriately.  The only thing is, it doesn't automatically adjust to only those cells in F that are not "n/a". If I understand you correctly, I have to manually do that with the filter setting.  That's what I'm trying to automate.  

Other then that, it appears to work.

B.
Adjusting-Graphic-v2.xlsm
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Bright01
Comment Utility
Could it be that we should use the "Selection_Change" statement instead of "Calculate" or "Change"?  When I do that, it appears to work but when I do a reset, I still can't reset the cells so that they all appear.

Just troubleshooting.

B.
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

I think it boils down to two small changes in the Macro.  In the "Sheet1" Macro, how do I automatically select the filter and uncheck n/a and in the Module for reset, how do I automatically reset the filter to "off"?

Does that sound right?

B.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Bright,

Sorry for the delayed response. Night and a few hours of sleep got in the way, followed by Saturday morning and family stuff.

But I had a little think.  How about dropping the macro solution altogether and doing the whole thing with some formulas from the trick box, a helper table, and applying the power of dynamic ranges?

See attached.

Column C has a formula that looks at Value1. If it's "n/a", the result will be a blank cell, if not, it will return the row number for the data table.

To the right of the chart is a helper table, which now is the source of the chart. A formula looks up all the values in the original source that do not have a blank in column C. This is the formula, starting in cell S4 and copied down and across:

=IFERROR(INDEX(E$4:E$15,SMALL($C$4:$C$15,ROW(A1))),"")

(IMPORTANT: if you copy this formula to another place in the workbook, or insert/remove rows above the formula, make sure that the Row() formula always uses A1 in the first row of the data table. If you would like this explained, please pipe up)

Whenever the original Value1 changes to "n/a", this helper table adjusts automatically to present a contiguous table without any blank rows.

Using dynamic ranges, we can capture just the populated rows of that table. These are the three required range names:


chtLabels      =Sheet1!$S$4:INDEX(Sheet1!$S:$S,LOOKUP(2,1/(Sheet1!$S$4:$S$15<>""),ROW(Sheet1!$S$4:$S$15)))
chtValue1      =OFFSET(chtLabels,0,1) -- for the Value1 data
chtValue2      =OFFSET(chtLabels,0,3) -- for the Difference data

The chart source in the attached file now points to the range chtLabels for the category labels, and the two data series take their values from chtValue1 and chtValue2

Have a play and tell me if this would work.

The attempted macro solution with the filter presented a few challenges with dynamic updates, and if you want to avoid manual intervention like clicking buttons, a formula solution might be a better way. It is entirely possible to have the chart source tucked away on another sheet, if you so wish, and even hide that sheet. The chart will still be fine.

As an added bonus, if you do not want to change the chart's vertical axis order, you can use LARGE() instead of SMALL() in the formula in S4 (copied down and across) :-))

cheers, teylyn


Adjusting-Graphic-v1--1-.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

This is a very "elegant" solution and similar to two others I have seen from you in the past using range names and a helper table.  How do you come up with these solutions??  It's a very unique skill to make Excel Graphics become dynamic like this.  You are truly talented with a mathmatical and creative mind ( I've seen your art work!).  

So, let me work with this for a couple of hours and get back with you.  I hope none of this has taken you away from quality family time.  Back with you shortly.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello Bright,

Thanks for your comments and praise.

       >>How do you come up with these solutions?

LOL. I'm not a VBA wiz. I'm a formula girl. With VBA, I'm out of my depth, unless it's really, really basic. I'm much more comfy with formulas, and, for the most part, they're faster than VBA, too!!

Almost half past midnight. I'll be out of circulation for a few hours, happy to discuss your findings then.

Have a good weekend. Don't forget to take some personal time, too!

cheers, teylyn
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

I hope this response finds you sound asleep.  However, when you check in, I found a slight error.  When you bring up your workbook, take a look at this;

1.) change n/a in cell F7 to numeric (e.g. 1); then look at cell U15.  Somehow, when the cells expand, it's putting a different formula (where there is none until you make the adjustment) which I think is causing problems.  You can see what I mean if you set all Value 1s to 1 and set all Value 2s to 2.  You will see that we are missing the value in "L" in the chart.
2.) I also get an error if I set all values to n/a which is what happens when the chart is reset completely (before additional or new data is entered).  

I think this may be a simple fix for a "formula girl"!  ;-)  As for me, I've spent about an hour trying to figure out why it's changing the formula on the fly.

Thanks,

B.
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

After some more testing, I think the formula fix for #1 is rather simple and I may have that working.  The real problem is that the model cannot be set to -0- or completely n/a without a formula error.  I've learned about tracing dependencies but still cannot find the formula problem.  When you get a chance, try testing it with all n/a and see if that shows you the problem.

Much thanks in advance.

B.
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
OK, have a look at the attached.

#1: the formula in column V (for the Difference) was not filled down to row 15. My bad.

#2: this is caused by the Index formula in the range name chtLabels. When there is no data in the helper table, the Index formula can not build a valid range because the nested lookup returns an error. In the attached, I've changed the definition of the formula to return an empty range if no valid data is found. The chart seems empty, but it actually plots data from empty cells.

chtLabels =Sheet1!$S$4:INDEX(Sheet1!$S:$S,IFERROR(LOOKUP(2,1/(Sheet1!$S$4:$S$15<>""),ROW(Sheet1!$S$4:$S$15)),5))

cheers, teylyn
Adjusting-Graphic-v1--1-.xlsm
0
 

Author Closing Comment

by:Bright01
Comment Utility
Teylyn,

You are the Formula Queen!  Great work...thanks for hanging in there with me; This is a great solution.  I'm now going back to what you sent me in an earlier post concerning posting a graphic from another worksheet which is what I'm doing with these complex graphics.  Your help is very very much appreciated on this side of the planet.

All the best,

B.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now