Solved

Dynamic Graphic - Stacked Horizontal Chart

Posted on 2011-09-02
17
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
17 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36472620
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:Ingeborg Hawighorst
ID: 36472695
See attached for all three points above in action.

cheers, teylyn
Adjusting-Graphic-v1.xlsm
0
 

Author Comment

by:Bright01
ID: 36472776
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36472883
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
ID: 36472983
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:Ingeborg Hawighorst
ID: 36473130
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
ID: 36473602
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
ID: 36473672
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
 

Author Comment

by:Bright01
ID: 36473730
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
ID: 36474136
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:Ingeborg Hawighorst
ID: 36477122
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
ID: 36477706
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:Ingeborg Hawighorst
ID: 36477733
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
ID: 36477826
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
ID: 36477881
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:
Ingeborg Hawighorst earned 500 total points
ID: 36479160
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
ID: 36480353
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

730 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