2

I have a machine that runs on an irregular schedule, and records the power usage into an Excel file. The machine automatically turns on at a different time every day and is sometimes off for a few days. For example:

20/3/2017 13:00:00  0kW
20/3/2017 14:00:00  13.1kW
20/3/2017 15:00:00  12.9kW
20/3/2017 16:00:00  0kW
...
21/3/2017 13:00:00 0kW
21/3/2017 14:00:00 0kW
21/3/2017 15:00:00  12.5kW

Notice that a number of the entries show a usage value of zero. I want to plot a graph of the data showing only the periods when the values are non-zero, and have no gap for the excluded values.

What the graph looks like with the table I have:

What the graph looks like with the table I have

What I actually want - gap for zero is totally removed:

What I actually want - gap for zero is totally removed

How can I do that?

Improve this question
6
  • The data is also collected even when the machine is off, so there will be a lot of 0s and the line graph will look like a mountain-liked shape. I don't want the all the 0s included in the plotting so that the line will not go down to zero. Take the data i posted as an example, on 20/3/2017, only 14:00 and 15:00 will be plotted, then there will be no gap for the off time and 21/3/2017 15:00 will come after that.
    – SnowBlack
    Commented Mar 22, 2017 at 5:59
  • Ah, my edit may be inaccurate then. You might want to tweak the question, even add the info in your comment. So what you're actually looking for is to plot just the non-zero entries? (Are there any blanks to worry about?) I don't have immediate access to Excel (I normally use LO Calc), but there may be a setting to ignore zero-value data when you chart. If not, look at filtering and excluding zero-value records.
    – fixer1234
    Commented Mar 22, 2017 at 6:12
  • Yes, exactly. There are quite a lot of blanks. I am no expert in Excel so I don't know where to find these options. However, just now I have tried to blank all the cells that contain zero, the graph doesn't go down to zero as I wanted, but the gap still exists, I just want to know how to eliminate those gaps.
    – SnowBlack
    Commented Mar 22, 2017 at 7:02
  • Try filtering the data. My recollection is that you access filtering from the menu or ribbon. When you turn it on, each column will show a little pull-down arrow. Expand the arrow on the usage column and you should find an option to deselect zero values. It will then hide all of those rows so the chart won't be aware that those values exist. You should then get something like your second image.
    – fixer1234
    Commented Mar 22, 2017 at 7:32
  • Ok well, it does hide all of those rows and chart looks better now. However it still doesn't meet my requirement. imgur.com/DHLrqO9 As you can see from the table, X-axis has a 5 minutes interval, from 8.15am - 9am, it doesn't plot the zero's, but it still have 8.20am, 8.25am etc on the x-axis, so I actually want to remove those interval so that the next point of 8.15am is 9.00am instead of 8.20am.
    – SnowBlack
    Commented Mar 22, 2017 at 7:57

2 Answers 2

Reset to default
1

You need to remove 0 values from your data to let Excel understand you don't want to plot it.

Easiest way for that is to use a calculated column:
=IF(D2=0,"-",D2)

Theoretically, now you can set your chart now, and it'll exclude those points, however I couldn't set it up with a normal charts.

But it works with a pivot chart:

  • Select your data and go to insert - charts - pivot chart
  • Set:
    • rows: date time
    • values: non-zero; set "summarize field by" to average
  • right click on the chart - change chart type - 2d line chart

enter image description here

Update

If you want to exclude times with 0 also from X axis range, then just filter them out in pivot table (move "non-zero" column to filters)

Or, in this case you don't even need a calculated column, you can plot and filter directly your "values" column.

Improve this answer
5
  • I've become an infrequent user of Excel and some features work differently in LO Calc. I recommended in a comment to the OP to try filtering. Do you know if that will also work? (If not, I should probably delete my suggestion so I don't confuse people.) :-)
    – fixer1234
    Commented Mar 22, 2017 at 7:53
  • 1
    @fixer: no, filtering in the source range doesn't affect data in charts in Excel. Filtering in the pivot does have an effect, however it filters x axis too (this is a line chart, not a scatter (xy) plot)
    – Máté Juhász
    Commented Mar 22, 2017 at 7:56
  • Thank first. However, I don't want the time that contains zero being plotted on the x-axis, what I mean is, from the diagram u posted, since 20/3/17 20:00, 21/3/17 13:00, 21/3/17 14:00 contains zero value, I want the graph to plot 21/3/17 15:00 right after 20/3/17 16:00, without extending the line in between them.
    – SnowBlack
    Commented Mar 22, 2017 at 8:24
  • Then just filter your pivot table to show only times with values (add "non-zero" as filter and uncheck -)
    – Máté Juhász
    Commented Mar 22, 2017 at 8:25
  • @SnowBlack, if this solved your problem, consider accepting the answer by clicking the checkmark next to it. That indicates that the problem is solved, and helps other users by identifying proven solutions. It also awards a little rep to both of you for the effort.
    – fixer1234
    Commented Mar 22, 2017 at 20:29
0

I would use two helper columns to format your data, then plot them on an XY/Scatter Chart (line charts treat your horizontal axis as categorical data and can create unanticipated problems.

  1. Create a helper column and add your date and time so it's in one value.
  2. Create a helper column that converts your 0 values into NA()'s
    =IF(C1=0,na(),C1). If there's a chance that there could be a low value that's not 0 but you don't want plotted, consider using something like C1<1 to catch those values.
  3. Plot your helper Date/Time column as your X axis and your helper Power column as your Y axis.
  4. Format your data markers to show your actual data points.

Use NA()

It's worth considering that your original image is more accurate-when power is 0 its 0-even if the chart looks "mountain like".

Use 0's

The impact

Improve this answer

You must log in to answer this question.

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