Matching shape to data series 1

[BigTeeJay]

So the problem is, my manager wants to do a normal line graph with the data points for a data series. Then, he has two more series which are the standard error for the first series (essentially just two more series, slighly above, and slightly below the first, but not always parrallel to it).

He wants to have a polygon that is essentially what you would get if you drew a light gray polygon that followed the two standard error series (the upper and lower relative to the main/center series) and then was closed off on each end (first data point of the upper series was connected to the first data point of the lower series) with the main data series appearing on top of this polygon.

The purpose is to illustrate the standard error surrounding a given series of data (by having the filled polygon behind the primary series).

So what I've done is figured out, programatically how to build the polygon, BUT I cant figure out how to get it to line up correctly on the chart (i can build the shape, but havent been able to figure out how to align it properly to the 2nd two data series, and properly behind the primary series).

Does anyone know to get, essentially, the .Top, and .Left properties for a data point? (if I had that I could then line my polygon up right).

I am also open to any other ideas that anyone might have that would allow me to do the equivlant of having a filled polygon behind the main one that equates to the standard error.

Regards,
Tj

 

[SkipVought]

Hi,

Try using a stacked column -- the BOTTOM stack format with no fill no line: the TOP stack would be your polygon.

So the VALUE associated with the BOTTOM stack would be proportional to the LINE value minus some constant I would think.

Think that that might work?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[BigTeeJay]

                                                                         ----
                                                                     --------
                                                                ----------   
                                                            --------         
                                                       ----------            
                                                   --------                  
                                              ----------                     
                                           -------                           
                                 --------------                          ////
                                 ---------                            ///////
                                ------                           /////////   
                                -----                         ///////     ---
                              ---                        /////////   --------
                             ---                      ////////   ---------   
                           ----                  /////////  ----------       
                          ---                 ////////   --------            
                        ----              //////// ----------                
                        ---       /   ////////  --------                     
                      ---        //////////---------                         
                     ---         ///// --------                              
                   ----        //-----------                                 
                  ---         ///-----                                       
                 ---         ///-----                                        
#################-###########/#--############################################
              ---         ///----                                            
             ----        ////--                                              
            ---         ///---                                               
          ----        ////---                                                
         ---         ///---                                                  
        ---        ////---                                                   
      ---         ///---                                                     
     ----       ////---                                                      
    ---        ///----                                                       
  ----        ///----                                                        
 ---        /// ---                                                          
---        ////---                                                           
--        ///---

 

[SkipVought]

What is that supposed to mean?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[BigTeeJay]

The above ASCII pict kinda represents what I have to work with. You can kind of make out the upper line (standard error high), middle line (primary value), and the lower line (standard error low).

What I need to do now, is essentially play connect the dots with the upper and lower lines and create a shape that follows their path, and then connects on either end, and fills gray to better illustrate the margin of error for the primary value that is being plotted.

I am not quite following the stacked column concept yet, could you explain more? (a stacked column wouldnt really give me the depiction of the growth of the primary values over time would it?). Not saying its a bad idea, I'm just not following yet

Also, if it helps any, the Y axis is a numeric measurement, and the X axis are months (intervals of Y being measured).

Tj

 

[BigTeeJay]

(sorry, you saw the pict before I posted my explaination )

 

[SkipVought]

Post some sample data. I'll play with it and get back.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[BigTeeJay]

Not sure which will paste easier into Excel

Copy & Pasted directly from Excel (tab separated)...

Time	Sample	StdErr	High Band	Low Band
Oct Y1	200	2	202	199
Apr Y2	211	2.5	213.5	210.5
Oct Y2	215	2	217	214
Apr Y3	222	1.5	223.5	220.5
Oct Y3	229	2	231	228
Apr Y4	234	2	236	233

Space separated...

Time    Sample  StdErr  High Band  Low Band
Oct Y1  200     2       202        199
Apr Y2  211     2.5     213.5      210.5
Oct Y2  215     2       217        214
Apr Y3  222     1.5     223.5      220.5
Oct Y3  229     2       231        228
Apr Y4  234     2       236        233

 

[SkipVought]

I dummied up some data

Dt Line        Err1        Err2
A  74.30677261 66.87609535 14.86135452
B  31.15438108 28.03894297 6.230876216
C  88.30926537 79.47833883 17.66185307
D  6.427861516 5.785075364 1.285572303

Dt is the x-axis range
Line is the Line Chart range
Err1 is the BOTTOM stack range - Series formatted with NO LINE/FILL
Err2 is the TOP stack range - Series Copied a hexagon formatted grey

Here's what the chart looks like:

SURROUNDING each Line Data Point is a grey hexagon of varying height (Err2 units high)

Is this what you want to see?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[BigTeeJay]

Not quite... I wish I could send you a picture (if you want to send me an email, I will respond with one... send to travis.johnson (@at@) nwea (.dot.) org, remove crap between to keep spammers away).

Essentially there should be 1 line which will follow roughly in the center of the two standard error series (but not always/exactly in the middle, it all depends on the two standard error values). The two standard error series define the outer lines of what I would like to become a solid gray polygon that follows behind the primary line (all of these will be going from lower left to upper right, the degree of which would depend on the data in the series).

So, rather than putting the gray polygons (or hexagons) behind each DATA POINT, I want the gray polygon's outter edges to match the LINES produced by the upper/lower standard error lines.

Tj

 

[SkipVought]

Tj,

You want an ERROR BAND that bounds the Line representing your data.

This could be represented in a combination chart --

Line Chart for your Primary Data

Stacked Area Chart for your error band. -- the bottom series formatted with no line, no fill; the top series formatted grey.

Hope this helps

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[BigTeeJay]

Hey skip,
For the benefit of the board... your solution was exactly what I was looking for, and I will write up a quick how to or something to post here as soon as I digest it.

Something I've noticed though, my supervisor was wanting to have more than one set of Primary Data Lines + Error Bands per chart... does this sounds possible? (I have a feeling it wont be since we are using a stacked Chart).

Let me know your thoughts, and I will still write up what we have thus far for the benefit of others.

Tj

 

[SkipVought]

I would assume that each line series/error band would have its own color or shade?

What happens when one series crosses another?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[BigTeeJay]

Hmm...
Well, since we are using the Blank series to set the lower limit area for the error band, and its stacked, the only way (as far as I can tell as this point) to have more than 1 line/band pair is to use the 2nd Y axis (otherwise they continue to stack). This isnt ideal, but might work.

The hope is that where they intersect/overlap, there would be a color difference. I am trying to play with the transparency, but its grayed out (Format Series, Patterns tab, Area section, Fill Effects button).

Getting closer

 

[BigTeeJay]

To summarize Skip's solution....

Dummy data:
=================================================

[Time]  [Value]  [StdErr]  [StdErr Low]  [Error Band]
 1       99       2         97            4
 2       120      1.5       118.5         3
 3       132      3         129           6
 4       141      2         139           4
 5       149      2.5       146.5         5
 6       154      2         152           4

=================================================

Insert a Chart, using [Value] as the source for your first series (and name). And [Time] is the X axis.

Right click on the Chart, goto Source Data, then Add. Select the [StdErr Low] values for the value of this series, and an empty cell for the name. Hit OK. Then right click on the new series, goto Chart Type, choose Area as the type, and then Stacked area as the subtype, hit OK. Now change the fill and line as none (right click on series and choose Format Data Series) and then hit ok.

Now add another series (right click on chart, goto Source Data, Add button). Choose the [Error Band] column's values for this new series values, and the column header for the name.

You should now have a single line, with a thicker bar behind it that represents the Standard Error band.

The 2nd dataset you chose (since this is a stacked area) marks the bottom of the 3rd dataset, and the Standard Error band is +/- the primary line).

The only problem I still have is trying to figure out a way to have more than one pair of line & band per chart.

You can add another pair if you assign them to the 2nd Y axis (otherwise they continue to stack and the results dont represent that I wanted). The problem is then that they block each other (apparently the Transparency section under fill effects is grayed out because it is not yet available
in Office 2k).

I have a hackish/programatic solution I am trying to come up with, I will post it too in case any one finds it useful (assuming I get it to work completely


Tj