Solved

Complex 2 Axis Graphic

Posted on 2014-10-27
13
76 Views
Last Modified: 2014-12-03
EE Pros,

I'm stumped on how to recreate a visual graphic into an Excel Graphic where I can use new data to change the graphic's look.  Its a two Vertical Axis Graphic with Time on the Horizontal Axis and two different data sets on the Virticals.  Not sure how to make this work and may take multiple passes.  I intend to get one thing done at a time, work with it, hopefully modify it and then ask for some refinement.

Attached is my example of what I'm trying to recreate.  The actual numbers at this point, don't matter.  It's getting the graphic right so I can input the data and have it reproduce the graphic.

Thank you in advance.

b.
C--Data-Temp-Complex-2-Axis-Model.xls
0
Comment
Question by:Bright01
  • 7
  • 6
13 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hi Bright,

conceptually, there are a few things:

- you may want to use an XY Scatter chart with smooth lines instead of a line chart.
- you will need an X axis value for every data point. Currently your datasets 1 and 2 only have values (vertical placement)  but no relation to where in time they occurred (X axis placement)
- an XY scatter chart will bridge / interpolate time gaps in the data, so you don't need to have the exact same dates for dataset1 and dataset2
- for the "QEWS alert" and the "Client system alert" you can add two more series with just one data point each: the date for the X axis placement and a Y value that corresponds to where (vertically) this label should show. You can then use data labels or a text box to show the series name "QEWS alert" and use vertical error bars to drop a line with an arrow head to the X axis.

There are other elements like the dotted horizontal lines and the arrows that I can't quite place yet, but I'm sure with sufficient data they can be arranged.

Bottom line:

If you want to draw such nice, smooth curves, you need a few more data values and each value needs a date stamp.

Then we can start working out the details.

cheers, teylyn
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

Greetings!  I so enjoy working with you on graphics.

OK.... so I've added some additional data points.  Please feel free to modify any of the points or add to them if you like. I set it up as a Range in order to accomodate future data strings without having to recode the graphic (Datasets 1&2).  I've changed it to a scatter chart.   Not sure how to put in the overlay and not sure how to get the horizontal scale to match the beginning and end dates....they look right when I edit the set.

See what you think.... I really appreciate your help!   Hope all is well in Z.

b.
C--Data-Temp-Complex-2-Axis-Modelv2.xls
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello Bright,

all is well. Hope you are fine.

For the two alert values you have dates, but you also need Y values. How about using the max value of your other data. So in   D6 use =MAX($C$3:$Q$4) and copy down.

Now edit the chart source and add a series. Series name in A6, Series X in C6, Series Y in D6.

Format the series to show the data marker, but no line (so you can see it). Add Error Bars. I hate when Excel does not let you choose which ones to add and always adds the wrong kind. Delete horizontal error bars. Format the vertical error bars to  

Direction = minus
end style = no cap
Error amount = Percentage with 100 as the value

Format the error bar line with a color of your choice. If you Excel version has the line style, use a line style with an arrow head at the desired end.

Add a data label to the series. Format it to show the Series Name and set the Label Position to Center. Format the label with the desired fill color.

Repeat the process for the second alert.

screenshot
cheers, teylyn
C--Data-Temp-Complex-2-Axis-Modelv2.xls
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

I tried it but I don't think I follow your logic.  I Copied the second series of data (MAX) across (not down) and then tried to set up a second series.  How do I format that second series because now there are actually 5 Series..........  

Shouldn't I have a different set of values for the second set of Y data?

I'm afraid I'm out of my league on this one.

B.
C--Data-Temp-C--Data-Temp-Complex-2-Axis
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hi Bright,

The blue line and the red line for dataset1 and dataset2 have many data points.

For the two alerts you need only one data point each.  The data point consists of an X and a Y component. The X component is the date and determines where on the X axis the data point will be positioned. The Y component is a number and determines the vertical position of the data point.

My logic is to ensure that the label for the alert is always visible in the upper area of the chart, I could take the highest number from the two datasets and use that for the Y value.

When defining the new data series for the Client Alert, select A6 as the label, C6 as the X value and D6 as the Y value. For the QEWS Alert add a new data series with A7 as the label, C7 as the X value and D7 as the Y value.  Since I want the labels to be at the same height, they need the same Y value. This was easiest to do by copying the Max() function from D6 down to D7. If you want, you could point the QEWS Alert Y value to D6 instead, so you don't have to duplicate the value. If you want to change the Y position of the label, just change the number (or formula) in D6.

screenshot
After you add the data series to the chart,  select the data series. If you don't see the new data point, it may be formatted with no marker. Since we have only one data point in the series, there is no line, since a line requires at least two points, if I recall my physics teacher correctly.

Since you can't see the series and cannot click it on the chart, you can select it via the Chart Tools > Format ribbon. There is a drop down with all chart elements at the very left of that ribbon. Select the chart element and then click "Format Selection". Here you need to format the Marker to have one of the built-in markers, like a square or a triangle. By default the XY Scatter chart with smoothed lines does not show markers.

In your last file, I see you added a series called "A6". The data point is hidden behind the red label of the Client Alert series that has the same value as your new series. You could delete the Client Alert series and your A6 series will become visible (if you have formatted the marker to show).

The little table with the name, x and y for the two alerts can be anywhere on the spreadsheet. It does not have to be underneath the dataset1 and dataset2 figures. I hope I could explain why you do not need to copy the Max() function across.

Let me know how you get on.

cheers, teylyn
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

Exactly right. Let me work on this .... back with you in about 12 hrs.  I need to do more research on the data set(s).

Thank you for the insight and setting me streight.

B.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Bright01
Comment Utility
Teylyn,

I think I have it except for one issue.  There are only 2 data sets needed as you pointed out.  However, there should be two Y Axis points not one....... Sold, Claimed.  And the numers on the "Claimed" should be lower but track to the second Y Axis (on the right side.  My issue now is that I do not know how to build in the second axis.  I have put a sample Y2 Data set in so you can see what the numbers may look like (Row 8).

B.
C--Data-Temp-C--Data-Temp-Complex-2-Axis
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello,

First something not related to the chart: The max formulas in rows 6 and 7  refer to a range that is much larger than just the two data sets and the range includes the current cell. This is a circular reference.  I think we have established that you only need two Max() functions in column E,  and the formula should be =MAX($D$3:$R$4).

Now to the chart. You can select a data series and in its formatting dialog select to show it on the secondary axis.  That will then show the secondary Y axis.

I'm not quite sure what version of Excel you are using. The files you attach have no extension, so you may be on a Mac? I cannot show a screenshot for that. In any case, I have plugged the data into the source table and sent the "Claimed" series to the secondary axis.

The danger with such a scenario is that it is not obvious from the lines which axis they are on and what value a data point has. As a visual indicator, I have formatted the axis labels with the same color as the line that relates to it.

See if that helps.

cheers, teylyn
C--Data-Temp-C--Data-Temp-Complex-2-Axis
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

Thank you!  I almost have it!  I have put in the second axis but can't get them lined up (i.e. both have a starting point of -0- at the same point.  I think this may be a very small "tweek" at the formatting axis level.

Is there any way to produce the line that shows the exact Y axis intersection as in the dotted lines on the original graphic?  That may not be possible.

With that said, this looks very good!

I'm using Excel 2010 Windows 32 bit.



B.
C--Data-Temp-C--Data-Temp-Complex-2-Axis
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
>> I'm using Excel 2010 Windows 32 bit.

I wonder why your files are uploading without an extension. I can rename them and add ".xls" to the file name, and then they work.  Any idea why they don't have an extension? Also, if you are using 2010, why use the backward-compatible .xls format instead of the .xlsx? Maybe your files need to be compatible with many setups and .xls is your safest option.

With the "smoothed line" XY chart, values that are close to zero might cause Excel to extend the Y axis below 0, just to make sure that a smoothed curve can be drawn without cutting anything off. But you can manually format each Y axis and set their respective minimum value to 0.

See attached.

As I said before, with this representation, I have no indicator which axis belongs to which line, so I cannot really tell what value any point in a line actually represents. You MUST give the reader that information. Is the end point of the red line 8000 or 26000? I think this is what the arrows in the sample chart image are for, but even with the arrows it is not cut and dried.

Greetings from San Francisco.

Cheers, teylyn
C--Data-Temp-C--Data-Temp-Complex-2-Axis
0
 

Author Comment

by:Bright01
Comment Utility
Teylyn,

SF..... one of the world's great cities!  I hope you are having a great time.

So I modified it and saved it as an XLSX file....hopefully it comes through that way.  I have posted two of the same files with different names and from different locations on my computer to see if that fixes the upload problem.

You have done a great job working with me on this and I think I now have it just right except for one thing.  I don't think this is an easy ask so I'm not expecting a fix to this but would like your opinion.  I have labeled the axis's correctly and made it a point chart rather then a smoothed line chart in order to get the Y Axis to line up.  That's ok.  But what is missing is the ability to identify the dotted line where the two Alerts intersect with both Y Axis values.  In other words, if you look at the original graphic.... the Alerts show both "Sold" and "Claimed" values.  The idea in automating this was to replicate the graphic so that you could change dates or values and the graphic would auto adjust.  It works just like that now but does not include the Y Intersection values in the Graphic.  This may be beyond what Excel can do.  I'm not sure but thought I would ask.

B.
C--Data-Temp-C--Data-Temp-Complex-2-Axis
C--Complex-Model-v6.xlsx
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Sorry for the delay. Just coming up for air after a busy week.

The second file worked for me.  

You can get the dotted lines if you add two more data series, each with a single data point only. One series goes on the primary axis (sold dotted line) and the other one on the secondary axis (claimed dotted line).

The values for the two series are derived from the date of the event (for the X axis position) and the value of the respective line series for that date. I use an Index/Match to find the correct date from the source data table. Alternatively, you could type it in manually.

Then add error bars to each series. Delete the Y error bars and format the X error bars to have only one bar.

The blue dotted line is a negative X error with a custom value in cell F11. The value in that cell is the difference in days between the event date and the earliest date on the chart (the crossing point between X and Y axis).

The red dotted line is a positive X error with a custom value in cell F12. The value in that cell is the difference in days between the largest date on the chart and the event date.

The error bars cause Excel to extend the X axis range, so you need to manually edit the X axis and set its minimum and maximum values to the respective numbers that represent the earliest and latest date in the data set.

Format the data points for these two series to have no markers, so they are invisible. Then select and delete their entries in the legend.

See attached.

cheers, teylyn
C--Complex-Model-v6.xlsx
0
 

Author Closing Comment

by:Bright01
Comment Utility
Outstanding work!  I've used this graphic 4 times now and it works perfectly!  Sorry it took a while to get back with you; my account got suspended due to a change in my CC (had to cancel and re-order).   Off to S. Korea next week.  Hoping to get to NZ in 2015 (Wellington).

Thanks again for outstanding work.  

B.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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

12 Experts available now in Live!

Get 1:1 Help Now