2

I have an excel graph that displays price over time for a cloud service account, but I'd like to add on to it events that would change the pricing (ex: "Discontinued vm operation" or "cleared out unused files")

The data series is based on the azure invoice output, but the events do not coincide with the X-series data points. Example: Azure is billing me on the 4th of the month every month for 12 total data points, but the events can happen on any given day.

The top graph below is what I have now, the bottom is what I want: What I got vs what I want

This is the data I'm working with:

Overall Price

Overall Price

Events

Events

7
  • Use a secondary axis?
    – mcalex
    Mar 5, 2018 at 16:06
  • When I try this, the line series crushes up against the left side, then the X-Y scatter ends up crushed along the right side. I assume this has something to do with the date ranges not linking up. I come from a world where you just "add" the records in to the domain of x-values and just declare a new series for the y values, but that doesn't seem to be available here.
    – C Bauer
    Mar 5, 2018 at 16:09
  • Generally for something like this you'll want to use an XY/Scatter chart for everything-not a combo chart that includes a line chart component. Line charts use a categorical axis, so it can't easily match the XY horizontal axis.
    – dav
    Mar 5, 2018 at 16:17
  • Please explain more clearly. Are the red lines the "events" you've added? Are they plotted on the graph or have you drawn them as an example? I mention this because the line on the right is not vertical. If the latter, you can plot a vertical line by adding a new data series with only two points. The two x-values would be the date where you want the line to appear, and the two y-values would be 0 and 6000, or as high as you want the line to go. Also, what dav said - this needs to be a scatter plot. Mar 5, 2018 at 16:17
  • Red lines are "events" and I added them to illustrate what I want. Does the entire chart need to be a scatter plot? Right now the chart type is a "combo" with 1 line and 1 scatter, and the 2 tables are on separate sheets.
    – C Bauer
    Mar 5, 2018 at 16:24

2 Answers 2

2

I'd approach this just a little different than Bandersnatch (although the principle is the same).

Since you've already got your invoice data in an Excel Data Table (which is good), here's what I would do:

1) Create an XY/Scatter Chart using your Data Table for the primary data series, with:
x axis = Date
y axis = Amount

2) Create a second table for your event data. You'll need 3 columns Date, Amount, Description.

3) The Date and Description columns you can pull from your current table. The simple way to do this would be to add your Event series using Date as your x-axis, and then using a single helper value (e.g. 0 or 6000) for your y-axis. This would align all of your points vertically, but not on your line.
What I would do is to interpolate the y-axis value for your date and use that as your y-axis series. Using a combination of structured names (since you're using Data Tables), and the FORECAST.LINEAR, MATCH, AND OFFSET formulas, your Event Table Amount Column formula would be something like this:

=FORECAST.LINEAR([@Date],
OFFSET(tbl_Overall_Price[[#Headers],Date]],
MATCH([@Date],tbl_Overall_Price[Date],1),1,2,1),
OFFSET(tbl_Overall_Price[[#Headers],[Date]],
MATCH([@Date],tbl_Overall_Price[Date],1),0,2,1))

enter image description here

4) Then, add your Event data series to your chart and format to taste.

enter image description here

5) Now that Excel 2016 allows you to use a cell range for Data Labels, just use your Description column for your labels.

1
  • That's pretty slick, dav. I like it a lot. +1 Mar 5, 2018 at 21:20
0

I'm not exactly sure what you're asking, but here is a graph of your data with lines added at the dates in your second table.

This is a scatter graph created from this data:

enter image description here

The red lines are the two additional series, added by specifying the two lower boxed areas in the data table as the x- and y-values for the two series. You could label the lines with text boxes to identify the associated events.

enter image description here

3
  • I tried this just now, when I add data series 2 as "Events" and put the X values as the dates of the events, then set the y values to "1", I just get a single dot in the wrong spot on the graph. I also have an extra X axis on top of the graph, and another one to the right side, so I'm not having an easy time of it! This is strange because treemaps, pie charts and a plain line chart never trip me up.
    – C Bauer
    Mar 5, 2018 at 19:07
  • You have to have two data points to form a line. So you have to have the data arranged as I have shown. When you add the series, enter the range for the x-values for Series2 as (for example) A:15:A16 and the y-values as B15:B16. But use the appropriate ranges for where your data is located. Mar 5, 2018 at 19:15
  • Note this has to be a scatter graph for the added series to line up with the correct dates. It's OK for the events to be on a separate sheet. Mar 5, 2018 at 19:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .